How to get column wise sum in SQL?-CodePudding

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?

CodePudding user response：

Try this:

With temp as 
(    --your query from above
    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"
)

select t.* from temp t
UNION
select sum(t1.colA),sum(t1.colB),sum(t1.colC) from temp t1