sqlt-sqlgroup-byazure-synapse-analytics

GROUP BY with multiple nested queries T-SQL


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.


Solution

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