CODEWITHSUNDEEP
snowflake-cloud-data-platform
jay
jay

Reputation: 1463

Using Group By And Over clause on Different columns

I have a table like this;

col 1|col 2|col 3
a    | 2   | 10
b    | -1  | 10
a    | 10  | 10

The goal is to get output as;

col 1|  col 2| col 3
a    | 12    | 30
b    |  -1   |30

I tried the following query;

select col 1,
sum(col 2),
sum(col 3) OVER()
From table t1
group by col 1

But I got the error; col 3 is not a valid group by expression. Kindly suggest an alternate solution.thanks in advance.

Upvotes: 0

Views: 83

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521073

You may try taking the sum of SUM(col3) over the entire table:

SELECT
    col1,
    SUM(col2),
    SUM(SUM(col3)) OVER()
FROM table t1
GROUP BY col1;

If you want to use SUM as a window function, then you need to sum something which is available after GROUP BY has evaluated. SUM(col3) is available, while col3 is not.

Upvotes: 1

Related Questions