Quick-gun Morgan Quick-gun Morgan - 2 years ago 193
SQL Question

How to use SUM over GROUP BY in SQL Server?

I currently have:

SELECT Name, COUNT(*) as Total
FROM DataTable
WHERE Name IN ('A', 'B', 'C')
GROUP BY Name


Resulting output:

Name Total
--------------
A 2
B 5
C 3


Instead I want this:

Name Total
--------------
A 10
B 10
C 10


Here 10 is a total of 2 + 5 + 3 (total number of records with name = A/B/C)

How do I do this?

Answer Source

To get your desired result you can use SUM() OVER () on the grouped COUNT(*). Demo

SELECT Name, 
       SUM(COUNT(*)) OVER () as Total
FROM DataTable
WHERE Name IN ('A', 'B', 'C')
GROUP BY Name
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download