Count value in SQL

Question

I have got a table with ID's:

Id
===
1
2
2
3
1
2

And I want to create a table with two columns like this:

Id      COUNT
=============
1      2
2      3
3      1

How can I do that?

Answer 1

Let's say you called your table 'user', you can try this :

SELECT user.Id as ID, count(user.Id) as COUNT_ID
FROM user
GROUP BY ID;

Hope it helps, WaLinke

Answer 2

You have to group by your id.

Select id, count(1) as COUNT
from yourtable
group by ID
order by id

That way you tell your sql, that you want to count the number of rows per id.

If you need more examples feel free to google sql count. There are many good examples out there. Or check this stackoverflow question: How to use count and group by at the same select statement

Answer 3

You can use GROUP BY and Count(columnname) functions like this

SELECT
Id,
Count(Id) AS COUNT
FROM
tablename
GROUP BY Id

Answer 4

Something like this should work

SELECT id, COUNT(id) AS Expr1 FROM dbo.Table1 GROUP BY id