How to sum values if I don't have column to group it by

Question

I need to sum sales grouped by country, but I have to group them manually because I don't have any other way. Unfortunately, I don't have the column 'continent', but there are not too many countries on the list so I can do it manually. I can't create any new columns in the table, so I need to do it in a query.

For example:

country | sum of sales
Germany    1000
Italy      500
Canada     700
UK         1300
USA        3000

I would like to see the total sales for Europe and North America

continent | sum of sales
Europe          2800
North America   3700

Does your countries table have a continent column? How do you handle Turkey and Russia? — jarlh, Oct 11 '21 at 11:42
@jarlh, due sensitive date, I changed the logic behind my query. Honestly, I'm working with some names and I replaced them with countries. — Zole72, Oct 11 '21 at 11:51
If you can't add a column to the table, can you add a table, even just a temporary one? — HoneyBadger, Oct 11 '21 at 11:52
I can add a column to the table in a way to write for example CASE statement, but can't add it so it stays permanently. I'm so sorry if I'm bringing confusion, I'm pretty new in all this. — Zole72, Oct 11 '21 at 11:55

Guru Stron · Answer 1 · 2021-10-11T12:21:50.200

1

You should be able to combine case expression and in predicate, something along this lines:

SELECT CASE
    WHEN country in ('Germany', 'UK') THEN 'Europe'
    WHEN country in ('Canada', 'USA') THEN 'North America'
    END as continent,
    sum("sum of sales")
FROM table
GROUP BY 1

edited Oct 11 '21 at 12:21

answered Oct 11 '21 at 11:39

Guru Stron

102,774
10
95
132

I'm writing my query in PrestoDB and getting the following message: "java.sql.SQLException: [Simba][AthenaJDBC](100071) An error has been thrown from the AWS Athena client. SYNTAX_ERROR: line 2:26: IN value and list items must be the same type: array(varchar) [Execution ID: 55b867bb-8e4e-483b-a91f-215dcbeb464c]" – Zole72 Oct 11 '21 at 11:47
1

Just a small note: `case` _expression_, `in` _predicate_. – jarlh Oct 11 '21 at 12:03
@Zole72 was not able to reproduce, [this](https://pastebin.com/fDKTVd8S) works just fine for me. – Guru Stron Oct 11 '21 at 12:23

How to sum values if I don't have column to group it by

1 Answers1