SQL Count columns from other columns

I have this view generated after using LEFT JOIN over 2 tables (simplified example).

Tables:

T1: Id, ...other columns not used
+----+-----+
| Id | ... |
+----+-----+
| 1  | ... |
| 2  | ... |
+----+-----+
T2: Id, NewId (Foreign Key from T1), Status, ...other columns not used
+-----+-------+--------+-----+
| Id  | NewId | Status | ... |
+-----+-------+--------+-----+
| 1   | 1     | 1      | ... |
| 2   | 1     | 2      | ... |
| 3   | 1     | 2      | ... |
| 4   | 1     | 3      | ... |
| 5   | 1     | 1      | ... |
| 6   | 1     | 1      | ... |
| 7   | 2     | 0      | ... |
| 8   | 2     | 2      | ... |
| 9   | 2     | 1      | ... |
| 10  | 2     | 2      | ... |
+-----+-------+--------+-----+

​x
 
T1: Id, ...other columns not used+----+-----+| Id | ... |+----+-----+| 1  | ... || 2  | ... |+----+-----+T2: Id, NewId (Foreign Key from T1), Status, ...other columns not used+-----+-------+--------+-----+| Id  | NewId | Status | ... |+-----+-------+--------+-----+| 1   | 1     | 1      | ... || 2   | 1     | 2      | ... || 3   | 1     | 2      | ... || 4   | 1     | 3      | ... || 5   | 1     | 1      | ... || 6   | 1     | 1      | ... || 7   | 2     | 0      | ... || 8   | 2     | 2      | ... || 9   | 2     | 1      | ... || 10  | 2     | 2      | ... |+-----+-------+--------+-----+​

Current View:

SELECT
  T1.Id,
  T2.Status
FROM T1
LEFT JOIN T2 ON T1.Id = T2.NewId;

 
SELECT  T1.Id,  T2.StatusFROM T1LEFT JOIN T2 ON T1.Id = T2.NewId;​

View: (I got till here)

+----+--------+
| Id | Status |
+----+--------+
| 1  | 1      |
| 1  | 2      |
| 1  | 2      |
| 1  | 3      |
| 1  | 1      |
| 1  | 1      |
| 2  | 0      |
| 2  | 2      |
| 2  | 1      |
| 2  | 2      |
+----+--------+

 
+----+--------+| Id | Status |+----+--------+| 1  | 1      || 1  | 2      || 1  | 2      || 1  | 3      || 1  | 1      || 1  | 1      || 2  | 0      || 2  | 2      || 2  | 1      || 2  | 2      |+----+--------+​

The required view needs to have separate columns for each status value (which are exactly 0, 1, 2 or 3). 0 & 1 are kept in the same column.

Required View: (But I need this)

+----+------------+----------+----------+
| Id | Status 0/1 | Status 2 | Status 3 |
+----+------------+----------+----------+
| 1  | 1          | 1        | 1        |
| 2  | 2          | 2        | 2        |
+----+------------+----------+----------+

 
+----+------------+----------+----------+| Id | Status 0/1 | Status 2 | Status 3 |+----+------------+----------+----------+| 1  | 1          | 1        | 1        || 2  | 2          | 2        | 2        |+----+------------+----------+----------+​

I feel like I missing something basic. How can I get this view?

I don’t think we need Rank() stuff, and it’s a big query over 4 normalized tables (in practice), which is why I need an optimal solution. Subqueries work with inline SELECT queries, but they need JOINs as well. The production requirement has 10 columns for counts over 2 separate columns.

Answer

Use conditional aggregation:

select
    id,
    sum(case when status in (0, 1) then 1 else 0 end) status_0_1,
    sum(case when status = 2 then 1 else 0 end) status_2
    sum(case when status = 3 then 1 else 0 end) status_3
from mytable
group by id

 
select    id,    sum(case when status in (0, 1) then 1 else 0 end) status_0_1,    sum(case when status = 2 then 1 else 0 end) status_2    sum(case when status = 3 then 1 else 0 end) status_3from mytablegroup by id​

In your orignal query, this should look like:

select
    t1.id,
    sum(case when t2.status in (0, 1) then 1 else 0 end) status_0_1,
    sum(case when t2.status = 2 then 1 else 0 end) status_2
    sum(case when t2.status = 3 then 1 else 0 end) status_3
from t1
left join t2 on t1.id = t2.newid
group by t1.id

 
select    t1.id,    sum(case when t2.status in (0, 1) then 1 else 0 end) status_0_1,    sum(case when t2.status = 2 then 1 else 0 end) status_2    sum(case when t2.status = 3 then 1 else 0 end) status_3from t1left join t2 on t1.id = t2.newidgroup by t1.id​

Advertisement

Answer