how to aggregate the rows as count & only show 1 row as answer?-CodePudding

(Q: count the #customers with firstname Mary who purchased more than 10 items)

SELECT x.first_name, COUNT(y.customer_id) AS ordercount
FROM [customers] x
JOIN orders y ON x.customer_id = y.customer_id
WHERE x.first_name = 'Mary'
GROUP BY x.first_name
HAVING COUNT(y.customer_id)>10
ORDER BY x.first_name ASC;

CodePudding user response：

Try it like this:

SELECT s.first_name, COUNT(s.customer_id) AS customercount
FROM (
    SELECT c.*
    FROM [customers] c
    JOIN orders o ON c.customer_id = o.customer_id
    WHERE c.first_name = 'Mary'
    GROUP BY c.customer_id
    HAVING COUNT(o.order_id)>10
    ) s
GROUP BY s.first_name

You have to find all of the customers with more than 10 purchases first (in the subquery) and then you can count how many there are (in the outer query).