SQLTeam.com | Weblogs | Forums

Summarise Data By 2 Fields Help - Count() Won't Reset

sql2008

#1

Hi All,
I am trying to build a table split by 2 different table fields to create a stacked column chart. It will be Client subdivided by Category, I can get it to display the clients and subdivide by category, but it shows the same total for each client - I can't work out how to get it to reset the count each time. My code thus far is:

SELECT DISTINCT
Client,

(SELECT ISNULL(COUNT(Client),0)
FROM MV_BREAKDOWNS
WHERE SERVICE LIKE 'Incidents & Accidents'
AND
CATEGORY LIKE 'Near Miss')
AS 'Near Miss',

(SELECT ISNULL(COUNT(Client),0)
FROM MV_BREAKDOWNS
WHERE SERVICE LIKE 'Incidents & Accidents'
AND
CATEGORY LIKE 'First Aid')
AS 'First Aid',

(SELECT ISNULL(COUNT(Client),0)
FROM MV_BREAKDOWNS
WHERE SERVICE LIKE 'Incidents & Accidents'
AND
CATEGORY LIKE 'LTI')
AS 'LTI',

(SELECT ISNULL(COUNT(Client),0)
FROM MV_BREAKDOWNS
WHERE SERVICE LIKE 'Incidents & Accidents'
AND
CATEGORY LIKE 'Fatality')
AS 'Fatality'

FROM MV_BREAKDOWNS

WHERE SERVICE LIKE 'Incidents & Accidents'

So as an example I have the following dataset;
Client 01
Category 01 - 3
Category 02 - 1
Category 03 - 4

Client 02
Category 01 - 5
Category 02 - 10
Category 03 - 2

It is showing both as the totals i.e.
Category 01 - 8
Category 02 - 11
Category 03 - 6

Any help greatly appreciated!

Thanks.