How to group the records at ID level and only select associated counts based on whether columns are-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

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