sqlsnowflake-cloud-data-platform

Conditional CASE WHEN select snowflake SQL

I am stuck on a conditional snowflake select sql. I am trying to count the IDs when they have the corresponding categorial value. I would appreciate some help.

Thanks

SELECT 
YEAR(DATETIME) AS YEAR, 
WEEKOVERYEAR(DATETIME) AS WEEK,
COUNT(CASE WHEN ID THEN CATEGORY = 'A')
from table 
group by week, year;
Solution

  • Here is one method:

    SELECT YEAR(DATETIME) AS YEAR, 
       WEEKOVERYEAR(DATETIME) AS WEEK,
       SUM(CASE WHEN CATEGORY = 'A' THEN 1 ELSE 0 END) as num_a
FROM table 
GROUP BY week, year;