user2961127 user2961127 - 4 months ago 12
SQL Question

Using SUM with group by clause

I have sql statement with group by clause as follows:

select number , count (*) as 'count' from mytable
group by number


Output:

number count
1 10
2 5
3 6


How should i modify my query to get the total in the table as follows:

number count
1 10
2 5
3 6
total 21

Answer

As you are probably using SQL Server, you can use with rollup:

select coalesce([number], 'total') as [number]
     , count(*) as [count]
from [mytable]
group by [number]
with rollup