GROUP BY with multiple nested queries T-SQL

Question

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:

• Total Received complaints per year and month
• Received complaints per year and month which got Cancelled
• Received complaints per year and month which got Resolved, etc...

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.

Answer 1

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);