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

(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;
sql


Solution 1:[1]

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).

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 RBarryYoung

Related Questions