带别名的 SQL Rollup

Question

我有这个sql查询

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)

它给了我结果集的总和，但有一个空格。

我希望汇总行以特定别名出现，例如:“资金转账总额”。我该如何实现？

这是我的查询结果，我需要在最后一行加上总计

谢谢

Answer 1

试试这个

   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