I am trying to perform multiple counts based on different conditions and group the results by year and month.
I have a complaints table, and I want to count:
I am using multiple nested select statements to do a count for each scenario and they work on their own. However, when I run the whole query I get an error: Column 'db.CustomerComplaints.id_Contact' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Please see my code below:
SELECT
YEAR(ReceivedDate) AS 'Year',
FORMAT(ReceivedDate, 'MMMM') AS 'Month name',
COUNT(*) AS 'Received Complaints'
,
(SELECT COUNT(*)
FROM db.CustomerComplaints t
WHERE t.status = 'Resolved'
AND t.id_Contact = cc.id_Contact
) AS 'Resolved Complaints'
,
(
SELECT COUNT(*)
FROM db.CustomerComplaints t
WHERE t.status = 'New'
AND t.id_Contact = cc.id_Contact
) AS 'New Complaints'
FROM db.CustomerComplaints cc
LEFT JOIN db.ReferralUpdates r
ON cc.id_Contact = r.Reference
WHERE r.ReferenceCode = 'Project1'
GROUP BY YEAR(ReceivedDate), FORMAT(ReceivedDate, 'MMMM')
What I want to get as a results is:
Year | Month | Received Complaints | Resolved Complaints | New Complaints |
---|---|---|---|---|
2023 | March | 5 | 5 | 0 |
2023 | April | 15 | 10 | 5 |
2024 | March | 7 | 4 | 3 |
I hope my question make sense.
Use conditional aggreation (CASE
expressions inside the aggregation functions (COUNT
).
SELECT
YEAR(receiveddate) AS year,
FORMAT(receiveddate, 'MMMM') AS month_name,
COUNT(*) AS received_complaints,
COUNT(CASE WHEN status = 'Resolved' THEN 1 END) AS resolved_complaints,
COUNT(CASE WHEN status = 'New' THEN 1 END) AS new_complaints
FROM hug2.customercomplaints
WHERE id_Contact IN
(
SELECT reference
FROM hug2.referralupdates
WHERE referencecode = 'Project1'
)
GROUP BY YEAR(receiveddate), MONTH(receiveddate), FORMAT(receiveddate, 'MMMM')
ORDER BY YEAR(receiveddate), MONTH(receiveddate);