How to group the records at ID level and only select associated counts based on whether columns are NULL or NOT NULL?

user_9320568 0
Viewed 37

I have the below record set, which is created by joining two tables.

A.ID A.COMPANY_CODE A.COUNTRY B.CO_ID B.CNTRY COALESCE(B.CO_ID, B.CNTRY) CNT
1 1234 United States of America null United States of America United States of America 33
1 1234 United States of America 1234 null 1234 6
1 1234 United States of America 1234 null 1234 1
2 5678 United States of America null United States of America United States of America 33

I want to group by ID and get the sum(CNT) for each ID. In case B.CO_ID is present, it should only select the associated CNT and ignore CNTRY for that ID. And when CO_ID is null, I want to sum based on Country. Below is the desired output -

A.ID SUM(CNT)
1 7
2 33

I tried writing a case condition but it is not working. Any guidance would be super helpful. Please let me know if I need to provide any additional details.

databricks-sql tsql sql
2 Answers

Seems like you just need conditional aggregation and a CASE condition

SELECT
  t.ID,
  CASE WHEN COUNT(t.CO_ID) = 0
    THEN SUM(t.CNT)
    ELSE SUM(CASE WHEN t.CO_ID IS NOT NULL THEN t.CNT END)
  END AS CNT
FROM YourTable t
GROUP BY
  t.ID;

db<>fiddle

Note that COUNT(SomeValue) counts the number of non-null values.

user_148689970

a CASE WHEN helps to select the correct correct numbers

The CTE even for the derived table, must be as TSQL doesn't like EXOSTS in grouping funct1ons

CREATE TABLE derived_table (
  
  "ID"    INT,
  "COMPANY_CODE" INT,
  "COUNTRY" varchar(50),
  "CO_ID" INT,
  "CNTRY" varchar(50),
  "CNTRY2" varchar(50),
  "CNT" INT
);

INSERT INTO derived_table
  ("ID",  "COMPANY_CODE" , "COUNTRY", "CO_ID",     "CNTRY", "CNTRY2",     "CNT")
VALUES
  (1,     1234,   'United States of America',     null ,  'United States of America' ,    'United States of America',     33),
  (1,     1234,   'United States of America',     1234,   null,   '1234',     6),
  (1,     1234,   'United States of America',     1234,   null ,  '1234',     1),
  (2,     5678,   'United States of America',     null,   'United States of America',     'United States of America',     33);

WITH CTE AS (SELECT ID
, CASE WHEN EXISTS(SELECT 1 FROM derived_table WHERE CNTRY IS NULL AND "ID" = d1."ID") AND CNTRY IS NULL THEN CNT
WHEN NOT EXISTS(SELECT 1 FROM derived_table WHERE CNTRY IS NULL AND "ID" = d1."ID") AND CNTRY IS NOT NULL THEN CNT
ELSE 0 END cnt_
FROM derived_table d1)
SELECT ID, SUM(cnt_) FROM CTE GROUP BY ID

ID | (No column name)
-: | ---------------:
 1 |                7
 2 |               33

db<>fiddle here

user_51935360
Related