Phil Phil - 1 month ago 8
SQL Question

Switch SQL result columns to rows and include a summary row beneath

I'd like the below columns to populate in the place of rows and then include a summary row beneath it:

table1
ID NAME Value Group
001 Bob 100 A
002 Don 200 A
003 Fay 300 B


Below is an example of the desired output:

GROUP NO SUM
Group A 2 300
Group B 1 300
Total 3 600

Answer Source
select coalesce('Group '+ [group], 'Total') [Group], count([group]) No, sum(value) SUM
from table1
group by [group] with rollup

http://sqlfiddle.com/#!6/655f4/3