Count Distinct Occurrences of Field, Group By Another Field-CodePudding

For a given shopId I'm trying to compute the number of unique users who qualified for all the coupons associated with a given shop.

I have a table with the following schema:

id |    type   | orderId | userId | couponId |  status   | shopId |                
---- ------------------ --------- -------- --------- ----------- -----
 39 | purchase |      89 |     33 |       1 | qualified |           18 
 43 | purchase |      90 |      5 |       3 | qualified |           18 
  1 | purchase |      68 |     32 |       1 | qualified |           18 
  2 | purchase |      69 |     32 |       3 | qualified  |           18 
  3 | purchase |      70 |     32 |       3 | qualified  |           18 
  4 | purchase |      71 |     38 |       1 | redeemed  |           18 
  5 | purchase |      72 |     39 |       2 | qualified  |           18 
  6 | purchase  |      73 |     30 |      9 | redeemed  |           11

On the below data set, if I supply the shopId 18, I want to obtain the result:

couponId | count 
1           2    (2 unique users (33, 32) qualified for coupon 1)
2           2    (1 unique user (39))
3           2    (2 unique users (5,32)

The below query allows me to compute total number of distinct users who qualified for coupon for a given shop, but how can I further break this down by coupon id?

SELECT COUNT(*) FROM (SELECT DISTINCT "userId" FROM "table" WHERE "shopId" = 18 AND status = 'qualified') AS temp;

CodePudding user response：

You can try to use COUNT with DISTINCT

SELECT couponId ,COUNT(DISTINCT userId) count 
FROM "table" 
WHERE "shopId" = 18 AND status = 'qualified'
GROUP BY couponId