Modify query so as to add new row with sum of values in some column-CodePudding

I have table in SQL Server like below using below code:

select col1, count(*) as col2, 
    case when col1 = 'aaa' then 'xxx'
         when col1 = 'bbb' then 'yyy'
         when col1 = 'ccc' then 'zzz'
    else 'ttt'
    end 'col3'
from table1
group by col1

col1  | col2  | col3
----------------------
aaa   | 10   | xxx
bbb   | 20   | yyy
ccc   | 30   | yyy

How can I modify my query in SQL Server so as to add new row with sum of values in col2? So I need something like below:

col1  | col2  | col3
----------------------
aaa   | 10   | xxx
bbb   | 20   | yyy
ccc   | 30   | yyy
sum   | 60   | sum of values in col2

CodePudding user response：

You could use ROLLUP for this. The documentation explains how this works. https://docs.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-ver15

select col1, count(*) as col2, 
case when col1 = 'aaa' then 'xxx'
     when col1 = 'bbb' then 'yyy'
     when col1 = 'ccc' then 'zzz'
else 'ttt'
end 'col3'
from table1
group by rollup(col1)

---EDIT--- Here is the updated code demonstrating how coalesce works.

select coalesce(col1, 'sum')
    , count(*) as col2
    , case when col1 = 'aaa' then 'xxx'
        when col1 = 'bbb' then 'yyy'
        when col1 = 'ccc' then 'zzz'
        else 'ttt'
        end 'col3'
from table1
group by rollup(col1)

CodePudding user response：

I tend to like GROUPING SETS for such items

Declare @YourTable Table ([col1] varchar(50),[col2] int,[col3] varchar(50))  Insert Into @YourTable Values 
 ('aaa',10,'xxx')
,('bbb',20,'yyy')
,('ccc',30,'yyy')
 
Select col1 = coalesce(col1,'sum')
      ,col2 = sum(Col2)
      ,col3 = coalesce(col3,'sum of values in col2')
 from @YourTable
 Group by grouping sets ( (col1,col3)
                         ,()
                        )

Results

col1    col2    col3
aaa     10      xxx
bbb     20      yyy
ccc     30      yyy
sum     60      sum of values in col2