Can the result set of inner query can be displayed with final result set

Question

I have a table that contains some data say

====================
Record  | Record_Count
1       |   12
3       |   87
5       |   43
6       |   54
1       |   43
3       |   32
5       |   65
6       |   43

I have a query that returns Record Count sum grouped by Record

select record,sum(record_count)
FROM table_name
WHERE <conditions>
GROUP BY tcpa_code
ORDER BY sum(record_count)

The result is something like this

====================
Record  | Record_Count
1       |   55
3       |   119
5       |   108
6       |   97

Now I also want a grand total of record_count (Sum of all record Count).

The thing is I want the above result set along with the grand total also.

I had tried this

select sum(subquery.record_count)
from (
select record,sum(record_count)
FROM table_name
WHERE <conditions>
GROUP BY tcpa_code
ORDER BY sum(record_count) ) as subquery

But by using this I am losing the individual record_count sum.

So my question is can I achieve result set that contains record_count sum for each record and grand total of record_count in a single query?

Are you using MySQL or Postgresql? – jarlh Jan 31 '18 at 11:35 — jarlh, Jan 31 '18 at 11:35

Yusuf Hassan · Accepted Answer · 2018-01-31T11:54:12.430

You may use union to achieve what you need:

(select cast(record as varchar(16)) record,sum(record_count) from schema.table
group by 1)
union
(select 'Grand_Total' as record,sum(record_count) from schema.table
group by 1);

Check here - SQL Fiddle

If your DB supports group by ... with rollup, you may also use:

select ifnull(record,'Grand Total')record,sum(record_count) total_count
from schema.table
group by record asc with rollup

Check here - SQL Fiddle

score 0 · Answer 2 · answered Jan 31 '18 at 12:56

To save some typing, a common table expression (cte) can be used:

with cte as
(
    select record, sum(record_count) rsum
    FROM table_name
    WHERE <conditions>
    GROUP BY record
)
select record, rsum from cte
union all
select 'Grand_Total', sum(rsum) from cte

score 0 · Answer 3 · answered Jan 31 '18 at 15:31

You should utilize windows functions of PostgrSQL.

As for this query,

SELECT record, record_count, sum(record_count) OVER()
    FROM (
         SELECT record, sum(record_count) record_count
           FROM table_name
           WHERE <conditions>
           GROUP BY tcpa_code
           ORDER BY sum(record_count) 
          ) as subquery

Can the result set of inner query can be displayed with final result set

3 Answers3