SQL multiple count for different condition as new column

Question

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.

Please use modern join syntax, instead of comma-separated tables in the FROM clause. — The Impaler, Sep 28 '21 at 13:38
Use a case expressions to do conditional aggregation. And remove table2.FLAG from the GROUP BY. — jarlh, Sep 28 '21 at 13:41
@Del Yes, table 1 has 1 row with unique id, table 2 has more rows which id = table1.id — Robotoflex, Sep 28 '21 at 13:52
It helps us to provide meaningful explanations that are accessible to the wider community if you include a minimal SQL data set, showing your current results, and the results you are expecting. — Chris Schaller, Sep 28 '21 at 14:04
Does this answer your question? [Oracle SQL pivot query](https://stackoverflow.com/questions/4841718/oracle-sql-pivot-query) — astentx, Sep 28 '21 at 17:02

Chris Schaller · Accepted Answer · 2021-09-28T15:20:15.413

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.

thanks @BarbarosÖzhan hard to let go of my bias against oracle i guess ;) — Chris Schaller, Sep 28 '21 at 15:20

score 1 · Answer 2 · answered Sep 28 '21 at 13:56

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.

SQL multiple count for different condition as new column

2 Answers2