CODEWITHSUNDEEP
mysql-workbench
18Man
18Man

Reputation: 572

how to count the buyer with special condition

so i have a table which contain data buyer who doing transaction per day in a month, so how do i get the buyer where the total transaction in a month >= 600k, this is my syntax 

SELECT
    b.buyer_id
FROM order_buyer b
WHERE
    EXISTS (SELECT 1 FROM order_buyer d
            WHERE d.buyer_id = b.buyer_id AND
                  d.createdAt >= '2017-10-01' AND d.createdAt < '2018-01-01') AND
    EXISTS (SELECT 1 FROM order_buyer d
            WHERE d.buyer_id = b.buyer_id AND
                  d.createdAt >= '2017-10-01' AND d.createdAt < '2018-01-01' ) AND
    NOT EXISTS (SELECT 1 FROM order_buyer d
                WHERE d.buyer_id = b.buyer_id AND d.createdAt < '2017-01-01') group by buyer_id;

Upvotes: 0

Views: 30

Answers (1)

Samir Selia
Samir Selia

Reputation: 7065

A simple query using HAVING clause should work for you.

SELECT buyer_id, amount
FROM order_buyer
WHERE createdAt >= '2018-10-01'
AND createdAt < '2018-11-01'
GROUP BY buyer_id
HAVING SUM(amount) >= 600000;

Upvotes: 1

Related Questions