I have a complex query where I am getting the count of various categories in separate columns.
Here's the output of my query:
district | colA | colB | colC ------------------------------------ DistA | 1 | 1 | 3 DistB | 2 | 0 | 2 DistC | 2 | 1 | 0 DistD | 0 | 3 | 4 .. And here's my query:
select q1."district", coalesce(max(case q1."type" when 'colA' then q1."type_count" else 0 end), 0) as "colA", coalesce(max(case q1."type" when 'colB' then q1."type_count" else 0 end), 0) as "colB", coalesce(max(case q1."type" when 'colC' then q1."type_count" else 0 end), 0) as "colC" from ( select d."name" as "district", t."name" as "type", count(t.id) as "type_count" from main_entity as m inner join type_entity as t on m."type_id" = t.id inner join district as d on m."district_id" = d.id where m."delete_at" is null group by d."name", t.id ) as q1 group by q1."district" I want to modify this query so that I can get the sum of each column in the last row, something like this:
district | colA | colB | colC ------------------------------------ DistA | 1 | 1 | 3 DistB | 2 | 0 | 2 DistC | 2 | 1 | 0 DistD | 0 | 3 | 4 .. Total | 5 | 5 | 9 I have tried using group by + rollup with the above query by just adding the following:
... group by rollup (q1."district") It adds a row at the bottom but the values are similar to the values of a row before it, and not the sum of all the rows before it, so basically something like this:
district | colA | colB | colC ------------------------------------ DistA | 1 | 1 | 3 .. DistD | 0 | 3 | 4 Total | 0 | 3 | 4 So, how can I get the column-wise some from my query?
