postgresql total column sum-CodePudding

SELECT

SELECT pp.id, TO_CHAR(pp.created_dt::date, 'dd.mm.yyyy') AS "Date", CAST(pp.created_dt AS time(0)) AS "Time",
       au.username AS "User", ss.name AS "Service", pp.amount, REPLACE(pp.status, 'SUCCESS', ' ') AS "Status",
       pp.account AS "Props", pp.external_id AS "External", COALESCE(pp.external_status, null, 'indefined') AS "External status"
  FROM payment AS pp
 INNER JOIN auth_user AS au ON au.id = pp.creator_id
 INNER JOIN services_service AS ss ON ss.id = pp.service_id
 WHERE pp.created_dt::date = (CURRENT_DATE - INTERVAL '1' day)::date
 AND ss.name = 'Some Name' AND pp.status = 'SUCCESS'


id    |    Date   |    Time   |   Service  |amount | Status |                 
------ ----------- ----------- ------------ ------- -------- ---
    9 | 2021.11.1 |  12:20:01 |  some serv | 100   | stat   |
   10 | 2021.12.1 |  12:20:01 |  some serv | 89    | stat   |
------ ----------- ----------- ------------ ------- -------- -----        
Total |           |           |            | 189   |        |

I have a SELECT like this. I need to get something like the one shown above. That is, I need to get the total of one column. I've tried a lot of things already, but nothing works out for me.

CodePudding user response：

If I understand correctly you want a result where extra row with aggregated value is appended after result of original query. You can achieve it multiple ways:

1. (recommended) the simplest way is probably to union your original query with helper query:

with t(id,other_column1,other_column2,amount) as (values
  (9,'some serv','stat',100),
  (10,'some serv','stat',89)
)
select t.id::text, t.other_column1, t.other_column2, t.amount from t
union all
select 'Total', null, null, sum(amount) from t

2. you can also use group by rollup clause whose purpose is exactly this. Your case makes it harder since your query contains many columns uninvolved in aggregation. Hence it is better to compute aggregation aside and join unimportant data later:

with t(id,other_column1,other_column2,amount) as (values
  (9,'some serv','stat',100),
  (10,'some serv','stat',89)
)
select case when t.id is null then 'Total' else t.id::text end as id
     , t.other_column1
     , t.other_column2
     , case when t.id is null then ext.sum else t.amount end as amount
from (
  select t.id, sum(amount) as sum
  from t
  group by rollup(t.id)
) ext
left join t on ext.id = t.id
order by ext.id

3. For completeness I just show you what should be done to avoid join. In that case group by clause would have to use all columns except amount (to preserve original rows) plus the aggregation (to get the sum row) hence the grouping sets clause with 2 sets is handy. (The rollup clause is special case of grouping sets after all.) The obvious drawback is repeating case grouping... expression for each column uninvolved in aggregation.

with t(id,other_column1,other_column2,amount) as (values
  (9,'some serv','stat',100),
  (10,'some serv2','stat',89)
)
select case grouping(t.id) when 0 then t.id::text else 'Total' end as id
     , case grouping(t.id) when 0 then t.other_column1 end as other_column1
     , case grouping(t.id) when 0 then t.other_column2 end as other_column2
     , sum(t.amount) as amount
from t
group by grouping sets((t.id, t.other_column1, t.other_column2), ())
order by t.id

See example (db fiddle):

(To be frank, I can hardly imagine any purpose other than plain reporting where a column mixes id of number type with label Total of text type.)