CODEWITHSUNDEEP
sqlpostgresql
Jason Smith
Jason Smith

Reputation: 127

Add a total at the bottom

I need to add a Total at the bottom of my table. Is this possible in my scenario?

select country, count(*) from customer
group by country

Country         Count
  USA              5  
  UK              10
 Canada           15 
 Russia           25
                  55  (Requested from SO community)

Upvotes: 2

Views: 4133

Answers (4)

Jay Ehsaniara
Jay Ehsaniara

Reputation: 1539

something like:

SELECT country
    , count(´1)
FROM customer
GROUP BY country

UNION

SELECT 'TOTAL'
    , count(1)
FROM customer;

Upvotes: 2

user330315
user330315

Reputation:

Use rollup()

select country, count(*) 
from customer
group by rollup (country )

If you want the label "Total" as well, you can use the grouping function: 

select case 
          when grouping(country) = 1 then 'Total' 
          else country 
       end as country, 
       count(*) 
from customer
group by rollup (country )

Online example: http://rextester.com/PKFE63954

Upvotes: 4

Muster Station
Muster Station

Reputation: 514

You could add another column to the row named Total

        DECLARE @Total int = 0;
        SET @Total = (SELECT COUNT(*) FROM customer);

        SELECT country, COUNT(*), [Total] = @Total
        FROM customer
        GROUP BY country

Upvotes: -1

Michael Betterton
Michael Betterton

Reputation: 114

You can artificially generate a row by using something like

select country
    ,count(*) 
from customer
group by country

UNION ALL

SELECT 'Total'
    ,COUNT(*)
FROM customer

Although this will affect any future calculations you make on this result set as it is a new row in the data.

Upvotes: 2

Related Questions