I need to get count of total count for the query I had in postgresql-CodePudding

I created a select query as following, now I need to get the total count of the "No.of Ideas generated" column in a separate row as total which will have a count of the individual count of particular idea_sector and idea_industry combination.

Query:

 select c.idea_sector,c.idea_industry,
count(*) as "No.of Ideas generated"
from hackathon2k21.consolidated_report c
group by idea_sector,idea_industry
order by idea_sector ,idea_industry

Output:

 ----------------------------------------------------------------------
   idea_sector         idea_industry                 No.of Ideas generated
    -----------------------------------------------------------------------
    COMMUNICATION-ROC   TELECOMMUNICATIONS            1 
    Cross Sector        Cross Industry                5 
    DISTRIBUTION        TRAVEL AND TRANSPORTATION     1 
    FINANCIAL SERVICES  BANKING                       1
    PUBLIC              HEALTHCARE                    1

Required output:

    ----------------------------------------------------------------------
       idea_sector         idea_industry                 No.of Ideas generated
        -----------------------------------------------------------------------
        COMMUNICATION-ROC   TELECOMMUNICATIONS            1 
        Cross Sector        Cross Industry                5 
        DISTRIBUTION        TRAVEL AND TRANSPORTATION     1 
        FINANCIAL SERVICES  BANKING                       1
        PUBLIC              HEALTHCARE                    1
------------------------------------------------------------------------
        Total                                             9

CodePudding user response：

You can accomplish this with grouping sets. That's where we tell postgres, in the GROUP BY clause, all of the different ways we would like to see our result set grouped for the aggregated column(s)

SELECT 
    c.idea_sector,
    c.idea_industry,
    count(*) as "No.of Ideas generated"
FROM hackathon2k21.consolidated_report c
GROUP BY
    GROUPING SETS (
       (idea_sector,idea_industry),
       ())
ORDER BY idea_sector ,idea_industry;

This generates two grouping sets. One that groups by idea_sector, idea_industry granularity like in your existing sql and another that groups by nothing, essentially creating a full table Total.

CodePudding user response：

The easiest way seems to be adding a UNION ALL operator like this:

select c.idea_sector,c.idea_industry,
count(*) as "No.of Ideas generated"
from hackathon2k21.consolidated_report c
group by idea_sector,idea_industry
--order by idea_sector ,idea_industry

UNION ALL
SELECT 'Total', NULL, COUNT(*)
from hackathon2k21.consolidated_report