CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Nick Knauer
Nick Knauer

Reputation: 4243

Add a column with count and sum by specific group in SQL

I have a dataset that looks like below: 

Dataset_1

   Date   Var1   Var2   Var3   Var4   Metric_A    Metric_B
10/1/19   NULL   NULL   NULL      A         10        NULL
10/1/19   NULL   NULL     AB      A       NULL          70
10/1/19   NULL   NULL   NULL      B         60        NULL
10/2/19   NULL      A     AC      B        100        NULL

How do I get the count by group (Date, Var1, Var2, Var4) and the sum of Metric A by the same group as a separate column without changing the column structure? I just want to add these two columns to the end of my current dataset

My expected output is below: 

   Date   Var1   Var2   Var3   Var4   Metric_A    Metric_B   COUNT_Var   SUM_A
10/1/19   NULL   NULL   NULL      A         10        NULL           2      10
10/1/19   NULL   NULL     AB      A       NULL          70           2      10
10/1/19   NULL   NULL   NULL      B         60        NULL           1      60
10/2/19   NULL      A     AC      B        100        NULL           1     100

My attempt was going to be below but it didn't work: 

SELECT *, 
COUNT(*) OVER (PARTITION BY Date, Var1, Var2, Var4), 
SUM(A) OVER (PARTITION BY Date, Var1, Var2, Var4) 
FROM Dataset_1

Upvotes: 1

Views: 359

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

From your data, var4 is defining the groups and nothing else:

SELECT d.*, 
       COUNT(*) OVER (PARTITION BY Var4), 
       SUM(A) OVER (PARTITION BY Var4) 
FROM Dataset_1 d

Upvotes: 1

Related Questions