SQL multiple count for different condition as new column-CodePudding

I have two tables with a column id and a flag. I want to get in one line the number of rows, where table2.id = some condition; With this request:

select table1.ID, table1.FLAG, count(1) row_count
from table1,
     table2
where table1.ID = 123
 and table1.ID = table2.ID
group by table1.ID, table1.FLAG, table2.FLAG

if table2.FLAG has 3 different values (Active, Suspend, Other), then I have 3 rows, the question is how to combine these three rows into one something like this: ID,FLAG,Active_Count,Suspend_Count,Other_Count.

CodePudding user response：

Just use a CASE statement of inline if to isolate the values you want in each column, this is a PIVOT or de-normalising technique without the overheads of setting up the PIVOT clause

SELECT table1.ID, table1.FLAG
 ,COUNT(CASE WHEN table2.FLAG = 'Active' THEN 1 END) as "Active"
 ,COUNT(CASE WHEN table2.FLAG = 'Suspended' THEN 1 END) as "Suspended"
 ,COUNT(CASE WHEN table2.FLAG = 'Other' THEN 1 END) as "Other"
FROM table1
INNER JOIN table2 ON table1.ID = table2.ID
WHERE table1.ID = 123
GROUP BY table1.ID, table1.FLAG

NOTE: SUM could have been used in place of count here, count works because the missing ELSE statement will resolve in a null, and Count will not count the nulls.

CodePudding user response：

Ok, so assuming that ID is unique in table 1, you are looking for something like this:

WITH test_data_1 (ID, FLAG) AS
(
  SELECT 123, 'Y' FROM DUAL UNION ALL
  SELECT 234, 'N' FROM DUAL
),
test_data_2 (ID, FLAG) AS
(
  SELECT 123, 'Active' FROM DUAL UNION ALL
  SELECT 123, 'Active' FROM DUAL UNION ALL
  SELECT 123, 'Suspend' FROM DUAL UNION ALL
  SELECT 123, 'Other' FROM DUAL UNION ALL
  SELECT 234, 'Active' FROM DUAL UNION ALL
  SELECT 234, 'Active' FROM DUAL UNION ALL
  SELECT 234, 'Other' FROM DUAL
)
SELECT *
FROM test_data_1 td1
INNER JOIN test_data_2 
  PIVOT (COUNT(*) AS COUNT FOR FLAG IN ('Active' AS ACTIVE, 'Suspend' AS SUSPEND, 'Other' AS OTHER)) td2 
  ON td1.id = td2.id;

Notice the use of PIVOT to format Table 2 before joining.