-1

Please have a look at my picture in which I have tried to explain what I want to achieve: each column should be the sum of values within the country and category. How will you suggest I solve this problem? There must be a standard approach by SQL user that I have overlooked.

I know I can work my way around it by something like

case when category = 'CatA' then sum(value) as 'CatA' and then a group by

but when my categories are too many this solution will be messy I will avoid it.

I am using Oracle SQL and Toad

EDIT: I have two answers but I think I have mis communicated what I whish. Basically what I wish to achieve is to avoid type in the categories manually.

enter image description here

econmajorr
  • 291
  • 1
  • 4
  • 10

2 Answers2

0

You want conditional aggregation. The case is the argument to the sum():

sum(case when category = 'CatA' then value end) as CatA

Also, don't use single quotes for column names -- this can leave to unexpected syntax errors and hard-to-debug run-time errors. Only use single quotes for string and date constants.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 3
    "but when my categories are too many this solution will be messy I will avoid it." (Not sure if there is a way around it though, you need to declare all the columns of the result after all, even in a pivot table) – Thilo Feb 18 '20 at 11:53
  • @Thilo thank you! Gordon Linoff, I still have to declare each category seperatly so this does solve my problem. Thank you though. – econmajorr Feb 18 '20 at 12:19
  • @Thilo is it possible to do it in a loop or something like that? :) – econmajorr Feb 18 '20 at 12:22
  • @econmajorr . . . You can construct the query using a spreadsheet or another query. – Gordon Linoff Feb 18 '20 at 13:34
0

You can use PIVOT as following:

Select *
FROM (select country, category, value from your_table)
PIVOT
(sum(value) for 
  category in ('CatA', 'CatB', 'CatC', ....your other categories like this...)
);

Cheers!!

Popeye
  • 35,427
  • 4
  • 10
  • 31
  • Thank you very much Tejash. However, this will still require that I type in alle the categories manually. Is there anyway I can avoid that? – econmajorr Feb 19 '20 at 11:35