Group by SQL with count
Lets say we have got rows like that:
MyTable
ID Name Product
------------------
1 Adam x
2 Adam y
3 Adam z
4 Peter a
5 Peter b
Using query like:
Select Name, Count(Product) from MyTable
group by Name
results will be:
Adam 3
Peter 2
But I would like results like:
1 Adam x 3
2 Adam y 3
3 Adam z 3
4 Peter a 2
5 Peter b 2
I hope Ypu know what I mean Could You help me with that query,开发者_StackOverflow thanks for help, Bye
You can join the table with a subquery run on the table to select the counts:
SELECT a.ID as ID, a.Name as Name, a.Product as Product, ISNULL(b.cnt,0) as Cnt
FROM MyTable a
LEFT JOIN (SELECT Name, COUNT(*) as Cnt FROM MyTable GROUP BY Name) b
ON a.Name = b.Name
How about?
Select *, Count() OVER(PARTITION BY Name) As C
from MyTable
Select a.Id,
a.Name,
a.Product,
IsNull(b.CountOfUsers,0) as CountOfUsers
From MyTable a
Left Join (Select Name, Count(Product) as CountOfUsers from MyTable
group by Name)b on a.Name = b.Name
;WITH c AS (SELECT Name, COUNT(Product) CountOfProduct
FROM MyTable
GROUP BY Name)
SELECT t.Id, t.Name, t.Product, c.CountOfProduct
FROM MyTable t
INNER JOIN c ON c.Name = t.Name
Use:
SELECT x.id,
x.name,
x.product,
COALESCE(y.name_count, 0) AS num_instances
FROM MyTable x
LEFT JOIN (SELECT t.name,
COUNT(*) AS name_count
FROM MyTable t
GROUP BY t.name) y ON y.name = x.name
COALESCE is the ANSI standard means of handling NULL values, and is supported by MySQL, SQL Server, Oracle, Postgre, etc.
精彩评论