Count unique customers with above zero bills-CodePudding

Will you please give a hint on the following problem?

My table contains information about customers and their bills at different stores. The problem is to count unique customers with above-zero bills broken down by stores in Mysql:

id | customer_id | store_id | bill_amount
-----------------------------------------
1  | 1           | A        | 10        | /* counts to store A */
2  | 1           | A        | 0         | /* doesn't count - non-unique at store A and zero bill */
3  | 1           | A        | 10        | /* doesn't count - non-unique at store A */
4  | 2           | A        | 0         | /* doesn't count - zero bill */
5  | 2           | B        | 10        | /* counts to store B */
6  | 2           | A        | 10        | /* counts to store A */
7  | 1           | B        | 10        | /* counts to store B */
8  | 3           | B        | 0         | /* doesn't count - zero bill */
9  | 3           | A        | 10        | /* counts to store A */
10 | 2           | B        | 10        | /* doesn't count - non-unique at store B */
11 | 1           | C        | 0         | /* doesn't count - zero bill */
12 | 3           | C        | 0         | /* doesn't count - zero bill */
-----------------------------------------

So, the desired result is:

store_id | unique_customers_count
---------------------------------
A        | 3 (id's 1, 6, 9)
B        | 2 (id's 5, 7)
C        | 0
---------------------------------

There is also a restriction: WHERE or HAVING clauses are not allowed to use unless in subqueries.

If the problem is solvable, please show me the solution or a way to the solution.

Please use this source to create and fill the table above. Omitting auto-increment intendedly.

create table customers (id int, customer_id int, store_id varchar(1), bill_amount int);

insert into customers values (1, 1, 'A', 10), (2, 1, 'A', 0), (3, 1, 'A', 10), (4, 2, 'A', 0), (5, 2, 'B', 10), (6, 2, 'A', 10), (7, 1, 'B', 10), (8, 3, 'B', 0), (9, 3, 'A', 10), (10, 2, 'B', 10), (11, 1, 'C', 0), (12, 3, 'C', 0);

CodePudding user response：

We can use conditional aggregation here:

SELECT store_id, SUM(bill_amount > 0) AS unique_customers_count
FROM
(
    SELECT customer_id, store_id, SUM(bill_amount) AS bill_amount
    FROM customters
    GROUP BY customer_id, store_id
) t
GROUP BY store_id
ORDER BY store_id;

The subquery above first aggregates by store and customer, taking the sum of the bill amounts. For total bill amounts greater than zero for a given customer within a store, we count one, otherwise we count zero.