CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Quick-gun Morgan
Quick-gun Morgan

Reputation: 338

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?

Upvotes: 3

Views: 866

Answers (3)

ChrisM
ChrisM

Reputation: 1586

If you count all of the records and then do a cross join on all the different names

SELECT a.NAME
    ,x.Total
FROM DataTable a
CROSS JOIN (
    COUNT(*) AS Total FROM DataTable
    ) x
GROUP BY a.NAME
    ,x.Total

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453908

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

Upvotes: 7

SqlZim
SqlZim

Reputation: 38063

Get rid of the group by and use distinct:

select distinct Name, count(*) over() as Total
from t
where name in ('A', 'B', 'C')

rextester demo: http://rextester.com/WDMT68119

returns: 

+------+-------+
| name | Total |
+------+-------+
| A    |    10 |
| B    |    10 |
| C    |    10 |
+------+-------+

Upvotes: 4

Related Questions