Search code examples
sqlgroup-bysumcase

SQL - sum entire column without group by and create a new column which is grouped

I have a situation where I want to sum the entire column, without grouping, but I also want the final output to be grouped for a particular country. For example, my input table looks something like this: enter image description here

I want the output to sum columns at overall level and then create a new column with the sum individually for each country, so for UK, entire Headcount_UK, gets summed enter image description here

I have tried using case and sum statement, but it is summing for that particular row and not the entire column.

 Select country,
                        SUM(CASE When country='UK' THEN (Headcount_UK)
                                     when country='ASIA' THEN (Headcount_Asia)
                        when country='USA' THEN (Headcount_USA)
                        when country='AFRICA' THEN (Headcount_Africa)
                     else 0 END) AS TOTAL
    from students
    group by country

Thank you

Solution

  • Two different methods below. The first uses a CTE with several UNIONed queries to get your totals; however, this calls the table many times. You'll probably want to use the 2nd method, which simply uses conditional aggregation. But your DBMS may not support this because it's also incorporating a windows function (sum over).

    Query #1

    with country_totals as (
  select 'UK' as country, sum(headcount_uk) as total from students union
  select 'ASIA', sum(headcount_asia) from students union
  select 'USA', sum(headcount_usa) from students union
  select 'AFRICA', sum(headcount_africa) from students
  )
select
 s.*, 
 ct.total
from students s
join country_totals ct
  on s.country = ct.country;
    country headcount_uk headcount_asia headcount_usa headcount_africa total
    UK 90 6 1 11 106
    ASIA 5 280 5 9 295
    USA 10 7 70 4 79
    AFRICA 1 2 3 25 49

    Query #2

    select *, 
 case 
  when country = 'UK' then sum(headcount_uk) over ()
  when country = 'ASIA' then sum(headcount_asia) over ()
  when country = 'USA' then sum(headcount_usa) over ()
  when country = 'AFRICA' then sum(headcount_africa) over ()
 end totals
from students;
    country headcount_uk headcount_asia headcount_usa headcount_africa totals
    UK 90 6 1 11 106
    ASIA 5 280 5 9 295
    USA 10 7 70 4 79
    AFRICA 1 2 3 25 49

    View on DB Fiddle