Group by SQL with count

Question

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, thanks for help, Bye

Answer 1

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

Answer 2

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

Answer 3

;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

Answer 4

How about?

Select *, Count() OVER(PARTITION BY Name) As C
from MyTable

Answer 5

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.