CODEWITHSUNDEEP
sqlgroup-bysnowflake-cloud-data-platform
johnnyheineken
johnnyheineken

Reputation: 613

Creating columns by subaggregating by condition in Snowflake SQL

I have following table:

id1 | id2  | n_products | daydiff
 a  |   1  |       12   |    12
 a  |   1  |       11   |    13
 a  |   1  |       90   |    46
 a  |   2  |       5    |    5
 b  |   2  |       15   |    15
 b  |   2  |       15   |    21
 c  |   3  |       90   |    7

I need to aggregate this table by id and using daydiff in the following manner:

this should be aggregated using mean.

The result should be:

id1 |    id2  |  sub 14 | 14_28 | 28+
 a  |     1   |    11.5 |    0  |  46
 a  |     2   |    5    |    0  |  0
 b  |     2   |    0    |    15 |  0
 a  |     3   |    7    |    0  |  0

How can I achieve this? I guess this would involve some group by statements, but I am not sure how should they be applied

Upvotes: 2

Views: 1276

Answers (2)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25903

Gordon's answer is cross platform correct, but for myself I prefer the snowflake IFF syntax

SELECT id1, id2,
    AVG(IFF(datediff < 14, n_products, NULL)) as avg_lt14,
    AVG(IFF(datediff >= 14 and datediff <= 28, n_products, NULL)) as avg_14_28,
    AVG(IFF(datediff > 29, n_products, NULL)) as avg_29pl
FROM t
GROUP BY id1, id2;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Use conditional aggregation:

select id1, id2,
       avg(case when datediff < 14 then n_products end) as avg_lt14,
       avg(case when datediff >= 14 and datediff <= 28 then n_products end) as avg_14_28,
       avg(case when datediff > 29 then n_products end) as avg_29pl
from t
group by id1, id2;

Some databases calculate the averages of integers as an integer. I don't know if Snowflake does this. If so, then change n_products to n_products * 1.0.

Upvotes: 3

Related Questions