Wsrtjtyk

Hoping I can articulate this correctly. I have 3 tables within a Risk & Control database.

Table 1 contains details on all Risks and their likelihood of occurrence. Risk ID is a unique value :

Risk ID |Risk Desc |Impact  | Likelihood

4363    | XXX      | High   | Likely

2357    | XXX      | Low    | Unlikely

7629    | XXX      | Medium | Unlikely

1929    | XXX      | Low    | Likely

Table 2 contains details on all Controls, and a rating on their effectiveness. Control ID is a unique value :

Control ID | Control Rating 

4566       | Effective

1431       | Not Effective

6724       | Effective

3415       | Effective

Table 3 contains a mapping of Risks with their linked controls in a 'many to many relationship' ie a risk can link to multiple controls and vice-versa.

Risk ID  | Control |

4363     | 4566    |

4363     | 1431    |

2357     | 4566    |

7629     | 6724    |

I am trying to create a view of all risk and an aggregated view of their control effectiveness, with 3 count columns. One that counts the total number of linked controls, one that counts the total number of EFFECTIVE controls and one that counts the total number of NOT EFFECTIVE controls.

The output would look like this :

Risk ID |Risk Desc |Impact  | Likelihood | Controls | Effective | Not Effective 

4363    | XXX      | High   | Likely     | 2        | 1         | 1

2357    | XXX      | Low    | Unlikely   | 1        | 1         | 0

7629    | XXX      | Medium | Unlikely   | 1        | 1         | 0

1929    | XXX      | Low    | Likely     | 1        | 0         | 1

I have managed to get the first part, however not sure how I add the other counts?

SELECT Table_1.RISK_ID,

       Table_1.RISK_DESC,

       Table_1.Impact,       

       Table_1.Likelihood,

COUNT(TABLE_2.CONTROL_ID) AS Total_Controls

FROM Table_1

INNER JOIN Table_2

ON Table_1.RISK_ID       = Table_2.RISK_ID

GROUP BY Table_1.RISK_ID,

         Table_1.RISK_DESC,

         Table_1.Impact,       

         Table_1.Likelihood

Any help greatly appreciated.

I'm not really sure if this is correct. In your sample tables you have no risk id in table two but your example shows you joining table one and table two on risk_id? So i'll just write this assuming your sample tables are correct.

    SELECT Table_1.RISK_ID,

       Table_1.RISK_DESC,

       Table_1.Impact,       

       Table_1.Likelihood,

COUNT(TABLE_2.CONTROL_ID) AS Total_Controls,

COUNT(CASE WHEN CONTROL_RATING IN ('Effective') THEN 'x' END) as effective,

COUNT(CASE WHEN CONTROL_RATING IN ('Not Effective') THEN 'x' END) as not_effective

FROM Table_1

INNER JOIN Table_3 

ON Table_3.Risk_ID = Table_1.Risk_ID

INNER JOIN Table_2

ON Table_2.CONTROL_ID       = Table_3.CONTROL

GROUP BY Table_1.RISK_ID,

         Table_1.RISK_DESC,

         Table_1.Impact,       

         Table_1.Likelihood

Sum will also work in this, but you wanted COUNT()

You could use simple math and aggregation for your "effective" and "not effective" columns. Pseudo Code:

Controls = COUNT(*)

Effective = SUM(CASE WHEN Control_Rating="Effective" THEN 1 ELSE 0 END)

Not Effective = SUM(CASE WHEN Control_Rating="Not Effective" THEN 1 ELSE 0 END)