user6638204 user6638204 - 8 days ago 5
SQL Question

SQL Rollup with alias

I have this sql query

Select
Case
When transfer.trf_type = 'c' then 'Transfer to own card'
When transfer.trf_type = 'o' then 'Transfer to own account'
When transfer.trf_type = 'I' then 'Transfer to a domestic bank'
When transfer.trf_type = 'b' then 'Transfer to another AIIB Customer'
End As Type ,
Count(transfer.trf_type) As total,
Sum (transfer.amount*currency.rate) AS totalSum
From transfer
Inner Join currency on transfer.currency = currency.currency
Where transfer.to_card IS null
Group By Rollup(Type)


It gives me the result set with the sum but with an empty space.

I want the rollup row to appear with a specific alias , eg :"Total fund transfers".How can i achieve this?

Here is my query result, I need to add total in the last row

enter image description here

thank you

Answer

Try this

   with grpSum as (Select
          Case 
        When transfer.trf_type = 'c' then 'Transfer to own card' 
        When transfer.trf_type = 'o' then 'Transfer to own account' 
        When transfer.trf_type = 'I' then 'Transfer to a domestic bank' 
        When transfer.trf_type = 'b' then 'Transfer to another AIIB Customer' 
      End As Type , 
      Count(transfer.trf_type) As total, 
      Sum (transfer.amount*currency.rate) AS totalSum 
    From transfer 
    Inner Join currency on transfer.currency = currency.currency 
    Where transfer.to_card IS null
        Group By ROLLUP(Type))
        select COALESCE(Type,'Total found transfers'),total,totalSum from grpSum