How to find count from two joined tables

Question

We have to find count for each risk category for impact level as shown in last result part

Risk Table

RiskID  RiskName
----------------------

1   Risk1
2   Risk2
3   Risk3
4   Risk4
5   Risk5
6   Risk6
7   Risk7
8   Risk8
9   Risk9
10  Risk10
11  Risk11

Category Table

Cat_ID  Cat_Name
--------------------------

1   Design
2   Operation
3   Technical

Risk_Category table

Risk_ID     Category_ID
------------------------

1       1
1       2
2       1
3       1
3       3
4       1
5       2
6       1
7       3
8       1
9       3
10      3

Risk_Impact_Assessment table

Risk_ID     Impact_Level    Impact_Score
---------------------------------------------

1       High        20  
2       Medium      15  
3       High        20
4       Low     10
5       High        20
6       High        20
7       High        20
8       Low     10
9       Medium      15
10      Low     15
11      Medium      15

Result should be like this

Cat_Name    Impact_Level_High   Impact_Level_Medium Impact_Level_Low
-------------------------------------------------------------------------------------

Design          1           1           2                               
Operation       2                                                   
Technical       2               2           1           

Answer 1

You probably want to use the group by clause, along with case, eg.:

select
 Cat_Name, 
 sum(case when Impact_Level = 'High' then 1 else 0 end) as [Impact_Level_High],
 sum(case when Impact_Level = 'Medium' then 1 else 0 end) as [Impact_Level_Medium],
 sum(case when Impact_Level = 'Low' then 1 else 0 end) as [Impact_Level_Low]
from [Risk_Impact_Assessment]
...
group by Cat_Name;

(I left out all the joins, I assume you can write these no problem)

You can use this trick to accomplish a lot of cool things, including parametric sorting and (just like here) complicated aggregate functions with little work.