Getting a count of a subset of GROUP BY records


Keywords:sql 


Question: 

I have a mail ledger table that I'm trying to query for return percentages. I am doing a GROUP BY to get unique records based on order_no, county and zip_code.

SELECT, COUNT(*) as TotalMailed, COUNT(*) (WHERE Returned = 1) as ReturnedMailed, Order_No, Zip_Code,County
FROM MailLedger
GROUP BY Order_No, Zip_Code, County

I am getting the proper count of each record grouping, however I need to get the count of the subset of grouped records that has Returned set to true.

I am running SQL Server, so I do have OVER and PARTITION at my disposal, however, I'd love to know the proper way to handle it in Postgres/MySQL as well.


1 Answer: 

In SQL Server, you can use conditional aggregation. I typically do this using SUM(CASE . . . ):

SELECT COUNT(*) as TotalMailed,
       SUM(CASE WHEN Returned = 1 THEN 1 ELSE 0 END) as ReturnedMailed,
       Order_No, Zip_Code, County
FROM MailLedger
GROUP BY Order_No, Zip_Code, County;

If Returned only takes on the values of 1 and 0/NULL, then you can do:

SELECT COUNT(*) as TotalMailed,
       SUM(Returned) as ReturnedMailed,
       Order_No, Zip_Code, County
FROM MailLedger
GROUP BY Order_No, Zip_Code, County;