Count Distinct from SQL Aggregation

user_3893580 0
Viewed 118

I have a table that looks like this:

store_id   cust_id   amount    indicator
1          1000      2.05      A
1          1000      3.10      A
1          2000      3.10      A
2          1000      5.10      B
2          2000      6.00      B
2          1000      1.05      A

What I'm trying to do is find the percent of sales with indicators A, B for each store by only looking at unique customer IDs (i.e., the two sales to customer 1000 at store 1 would only count once). Something like this:

store_id   pct_sales_A   pct_sales_B   pct_sales_AB
1          1.0           0.00          0.00
2          0.0           0.50          0.50

I know that I can use a subquery to find the counts of each transaction type, but I'm having trouble only counting the distinct customer IDs. Here's an (incorrect) approach for the pct_sales_A column:

SELECT
     store_id,
     COUNT(DISTINCT(CASE WHEN txns_A>0 AND txns_B=0 THEN cust_ID ELSE NULL))/COUNT(*) AS pct_sales_A --this is wrong
     FROM (SELECT store_id, cust_id,
           COUNT(CASE WHEN indicator='A' THEN amount ELSE 0 END) as txns_A,
           COUNT(CASE WHEN indicator='B' THEN amount ELSE 0 END) as txns_B
           FROM t1
           GROUP BY store_id, cust_id
           )
     GROUP BY store_id;
pivot count mysql sql database
2 Answers

You can use conditional aggregation with COUNT(DISTINCT):

SELECT store_id,
       COUNT(DISTINCT CASE WHEN indicator = 'A' THEN cust_id END) * 1.0 / COUNT(DISTINCT cust_id) as ratio_a,
       COUNT(DISTINCT CASE WHEN indicator = 'B' THEN cust_id END) * 1.0 / COUNT(DISTINCT cust_id) as ratio_a,
FROM t1
GROUP BY store_id;

Based on your comment, you need two levels of aggregation:

SELECT store_id,
       AVG(has_a) as ratio_a,
       AVG(has_b) as ratio_b,
       AVG(has_a * has_b) as ratio_ab
FROM (SELECT store_id, cust_id,
             MAX(CASE WHEN indicator = 'A' THEN 1.0 ELSE 0 END) as has_a,
             MAX(CASE WHEN indicator = 'B' THEN 1.0 ELSE 0 END) as has_b
      FROM t1
      GROUP BY store_id, cust_id
     ) sc
GROUP BY store_id;
user_11440350

I think you want two levels of conditional aggregation:

select 
    store_id,
    avg(has_a = 1 and has_b = 0) pct_sales_a,
    avg(has_a = 0 and has_b = 1) pct_sales_b,
    avg(has_a + has_b = 2) pct_sales_ab
from (
    select 
        store_id, 
        cust_id,
        max(indicator = 'A') has_a,
        max(indicator = 'B') has_b
    from t1
    group by store_id, cust_id
) t
group by store_id

Demo on DB Fiddle:

store_id | pct_sales_a | pct_sales_b | pct_sales_ab
-------: | ----------: | ----------: | -----------:
       1 |      1.0000 |      0.0000 |       0.0000
       2 |      0.0000 |      0.5000 |       0.5000
user_106767160
Related