sqlpostgresqlinner-query

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

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?

Solution

  • 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