CODEWITHSUNDEEP
mysqlsqlmysql-workbench
18Man
18Man

Reputation: 572

how to count data buyer who had special condition mysql

i have data buyer the table called order_star_member, on this table contain createdAt. that row contain the date of transaction, users_id contain the buyer, and total_price_star_member was the amount of the transaction of each buyer, so i want to counting the amount of buyer who doing transaction >= 600000 in january and then doing transaction again in february 2020 for >= 600000 too, i tried to make insert new table for january itself and for february itself but it's just wasting time, do you know how exact syntax to solve this problem? i've tried this syntax but idk why the result was 0 instead when i check manual, the answer is 5 buyer who doing transaction in january for >= 600000 transaction and also in february >= 600000

select count(*) from (SELECT 
    b.users_id
FROM order_star_member b
WHERE
    EXISTS (SELECT 1 FROM order_star_member d
            WHERE d.users_id = b.users_id AND
                  d.createdAt >= '2019-12-01' AND d.createdAt < '2020-01-01') AND
    EXISTS (SELECT 1 FROM order_star_member c
            WHERE c.users_id = b.users_id AND
                  d.createdAt >= '2020-01-01' AND d.createdAt < '2020-02-01') AND
    NOT EXISTS (SELECT 1 FROM order_star_member e
                WHERE e.users_id = b.users_id AND d.createdAt < '2019-12-01') group by users_id having sum(total_price_star_member) >= 600000) inner_query;

sample data:

January

February

Expected Result after doing syntax

Upvotes: 0

Views: 40

Answers (2)

Cocofalco
Cocofalco

Reputation: 71

The reason you are not getting 5 is the table alias references arent correct(replacing d with c and e)

select count(*) from 
  (SELECT b.users_id FROM order_star_member b WHERE 
        EXISTS (SELECT 1 FROM order_star_member d 
                WHERE d.users_id = b.users_id AND 
                d.createdAt >= '2019-12-01' AND
                d.createdAt < '2020-01-01')
        AND EXISTS (SELECT 1 FROM order_star_member c 
                WHERE c.users_id = b.users_id AND
                c.createdAt >= '2020-01-01' AND 
                c.createdAt < '2020-02-01') 
        AND NOT EXISTS (SELECT 1 FROM order_star_member e
                WHERE e.users_id = b.users_id AND
                e.createdAt < '2019-12-01') 
   group by b.users_id having
   sum(b.total_price_star_member) >= 600000) inner_query;

I would work on another group by level to deal with the grouping by month.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270643

If you want users whose sum of the transactions exceeds 600,000 in each of January and February, then you can use two levels of aggregation:

select user_id
from (select osm.user_id, month(osm.createdAt) as mon,
             sum(osm.price) as total_price
      from order_star_member osm
      where osm.createdAt >= '2020-01-01' and
            osm.createdAt < '2020-03-01'
      group by osm.user_id, month(osm.createdAt)
     ) um
where total_price >= 600000
group by user_id
having count(*) = 2;

Upvotes: 2

Related Questions