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.