CODEWITHSUNDEEP
sql
Batar
Batar

Reputation: 29

Join three tables with counts

I have these three tables (Soknad, Prognose and Did) in the SQL Server database:

Table Soknad has columns: S_ID (key), S_REFNR

Table Prognose has columns: P_ID (key), P_S_ID

Table Did has columns: D_ID (key), D_S_ID, Did_Something

Prognose.P_S_ID is foreign key to Soknad.S_ID. Did.D_S_ID is foreign key to Soknad.S_ID.

The tables are like this:

SOKNAD

  S_ID | S_REFNR |
  1    | abc     |
  2    | cbc     |
  3    | sdf     |

PROGNOSE

  P_ID | P_S_ID |
  10   | 1      |
  11   | 2      |

DID

  D_ID | D_S_ID | D_Did_Something |
  100  | 1      | 1               |
  101  | 1      | 1               |
  102  | 1      | 0               |
  103  | 2      | 1               |
  104  | 2      | 1               |

I want to join these tables (like a view or select statement). From the Did table a count of column Did_Something should be returned, as well as a count of the same column where the value is 1 (one). The result should be:

S_ID | S_REFNR | P_ID | Count_D_Did_Something | Count_D_Did_Something_Is_One |  
1    | abc     | 10   | 3                     | 2                            |  
2    | cbc     | 11   | 2                     | 2                            |  
3    | sdf     |      |                       |                              |

Any help would be appreciated!

Upvotes: 0

Views: 287

Answers (3)

Justin Russo
Justin Russo

Reputation: 2214

I believe what you want to do is join two tables and put the counts where the rows match the left table in separate columns for each table.

This would accomplish that.

select t1.id, count(t2.id) t2_count , count(t3.id) t3_count
from   table1 as t1 
       left outer join table2 as t2 on t2.table1_id = t1.id
       left outer join table3 as t3 on t3.table1_id = t1.id
group by t1.id;

To accomplish the counts you want based on criteria from one of the outer joined tables, you can do that this way, using a derived table...

select t1.id, count(t2.id) t2_count, count(tt2.mCount) Did_SomethingCount, count(t3.id) t3_count
from   table1 as t1 
       left outer join table2 as t2 on t2.table1_id = t1.id
       left outer join (select count(*), table1_id mCount from table2 where Did_Something = 1 group by table1_id) as tt2 on tt2.table1_id = t1.id
       left outer join table3 as t3 on t3.table1_id = t1.id
group by t1.id;

Upvotes: 1

Santhosh
Santhosh

Reputation: 1791

This should be simply give the results you need. Note, I simply summed up when you want count to be for values having one,

SELECT S.S_ID, S.REFNR, P.P_ID, COUNT(D.D_DID_SOMETHING) AS COUNT_D_DID_SOMETHING, 
SUM(D_DID_SOMETHING) AS COUNT_D_DID_SOMETHING_IS_ONE
FROM SOKNAD AS S
INNER JOIN PROGNOSE AS P
ON P.P_S_ID = S.S_ID
INNER JOIN DID AS D
ON D.D_S_ID = S.S_ID
GROUP BY S.S_ID, S.REFNR, P.P_ID

Upvotes: 0

Hogan
Hogan

Reputation: 70523

Here you go:

select s.s_id,
       p.p_id, 
       count(d.Did_Something) as Count_D_Did_Something, -- nulls won't be counted 
       sum(CASE WHEN d.Did_Something = 1 THEN 1 ELSE 0 END) as Count_D_Did_Something_is_one
from   Soknad as s 
       left join Prognose as p on p.P_S_ID = s.s_id
       left join Did as d on d.D_S_ID = s.s_id

Upvotes: 0

Related Questions