Aggregating by category & obtaining conditional counts

Question

My temporary able has 3 columns where an individual is unique by Category ID AND Customer ID (i.e. there can be multiple identical Customer IDs across categories; customer ID is not unique).

Category ID	Customer ID	Number of Purchases
X	A	4
X	B	10
X	C	2
Y	A	2
Y	E	6
Z	A	11
Z	C	5
Z	D	10

The output table I am looking for is basically 4 columns: grouped by category ID identifying total number of customers who have at least 1 purchases, customers who have at least 10 purchases as well as total purchases across all customers within that category. The output table would look like this:

Category_ID	Total_Customers_1	Total_Customers_10	Total_Purchases
X	3	1	16
Y	2	1	8
Z	3	2	26

When I input the following code, I get the correct total purchases column per category but the total number of customers who had at least 3 purchases per category is incorrect as the aggregates are identical across all categories.

How should I proceed? Thank you for your help.

Update: I forgot to add my code:

SELECT table.categoryID AS Category_ID 
        (SELECT COUNT (table.customerID) 
        FROM table 
        WHERE table.purchases >=1) AS Total_Customers_1, 
        (SELECT COUNT (table.customerID) 
        FROM table 
        WHERE table.purchases >=10) AS Total_Customers_10, 
    SUM (table.purchases) AS Total_Purchases, 
FROM table 
GROUP BY Category_ID, Total_Customers_1, Total_Customers_10,

(P.S When I try to just GROUP BY Category_ID, I get an ERROR that syntax is incorrect because I have sub-queries in my SELECT statement.

Please format your code to note just be a single line. New lines and indents matter for readability. — MatBailie, Sep 13 '21 at 14:39

MatBailie · Answer 1 · 2021-09-13T14:42:16.737

You can use conditional aggregation.

SELECT
  category_id,
  COUNT(*) AS total_customers_1,
  COUNT(DISTINCT CASE WHEN purchases >= 10 THEN customer_id END) AS total_customers_10,
  SUM(purchases)   AS total_purchases
FROM
  yourTable
GROUP BY
  category_id

I used the DISTINCT as defensive programming, but if a customer id only exists once in any given category, it's not required.

Notes:

CASE expressions without an ELSE clause default to ELSE NULL
Aggregates, such as COUNT(), exclude NULLs

Aggregating by category & obtaining conditional counts

1 Answers1