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

Question

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:

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

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

Answer 1

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

Answer 2

See example

with totals as(
  select 
     sum(headcount_uk)sum_uk
    ,sum(headcount_asia)sum_asia
    ,sum(headcount_usa)sum_usa
    ,sum(headcount_africa)sum_africa
  from students
)
select s.* 
  ,case when country='UK' then sum_uk
        when country='ASIA' then sum_asia
        when country='USA' then sum_usa
        when country='AFRICA' then sum_africa
    end totals
from students s, totals t