CODEWITHSUNDEEP
sqldb2
datatester
datatester

Reputation: 43

DB2 SQL to count number of rows with respective to 2 columns

I have data in DB2 table something like this

main_id sub_id key_id type
100 1001 1 0
100 1001 2 0
101 1002 1 0
102 1003 1 1
102 1004 1 1
103 1005 2 2
103 1006 2 2
104 1007 2 0
105 1008 2 1
105 1009 2 1

Now I am trying to create output something like this where :

count1 - main_id count with multiple rows only with key id 1
count2 - main_id count with multiple rows only with key id 2
count3 - main_id count with multiple rows with combo 1 & 2 key id
Type count_1 count_2 count_3
0 1
1 1 1
2 1

Overall, I need distinct main_id's count with multiple rows of sub_id and then need to segregate them based on type and key_id's

In the above case :

Ignored 101 and 104 as there are no multiple rows for that main_id

    WITH main_ids AS (
  SELECT main_id, key_id, type, COUNT(sub_id) sub_id_count
  FROM your_table
  GROUP BY main_id, key_id, type
),
grouped AS (
  SELECT type, COUNT(main_id) main_id_count
  FROM main_ids
  WHERE sub_id_count > 1
  GROUP BY type
)
SELECT type,
  SUM(CASE WHEN key_id = 1 THEN sub_id_count END) count_1,
  SUM(CASE WHEN key_id = 2 THEN sub_id_count END) count_2,
  SUM(CASE WHEN sub_id_count > 1 THEN sub_id_count END) count_3
FROM main_ids
JOIN grouped ON grouped.type = main_ids.type
GROUP BY type

Need help with count_3 condition, is there any way where can get count of main_id having multiple rows with both key_id 1 and 2(in the above case only 100 main_id is satisfying that condition)

Appreciate any help

Upvotes: 0

Views: 87

Answers (1)

Mark Barinstein
Mark Barinstein

Reputation: 12339

WITH 
  T (main_id, sub_id, key_id, type) AS
(
VALUES 
  (100, 1001, 1, 0)
, (100, 1001, 2, 0)
, (101, 1002, 1, 0)
, (102, 1003, 1, 1)
, (102, 1004, 1, 1)
, (103, 1005, 2, 2)
, (103, 1006, 2, 2)
, (104, 1007, 2, 0)
, (105, 1008, 2, 1)
, (105, 1009, 2, 1)
)
, G AS 
(
  SELECT TYPE, KEY_ID, COUNT (DISTINCT MAIN_ID) C
  FROM T
  GROUP BY TYPE, KEY_ID
)
SELECT 
  TYPE
, CASE WHEN C1 = 1 THEN 1 END AS COUNT_1
, CASE WHEN C2 = 1 THEN 1 END AS COUNT_2
, CASE WHEN C1 > 1 AND C2 > 1 THEN 1 END AS COUNT_3
-- Just for convenience
, C1
, C2
FROM 
(
  SELECT 
    M.TYPE
  , COALESCE (G1.C, 0) AS C1  
  , COALESCE (G2.C, 0) AS C2
  FROM (SELECT DISTINCT TYPE FROM G) M
  LEFT JOIN G G1 ON G1.TYPE = M.TYPE AND G1.KEY_ID = 1
  LEFT JOIN G G2 ON G2.TYPE = M.TYPE AND G2.KEY_ID = 2
)
TYPE COUNT_1 COUNT_2 COUNT_3 C1 C2
0 1 2 2
1 1 1 1 1
2 1 0 1

Upvotes: 0

Related Questions