Re:My Favorite PostgreSQL Queries and Why They Matter
by sudomake on Monday April 16, 2018 @02:18PM
(#16851374)
You dont need include aggregate functions in your select clause to use them in having and order by.
I suppose I may be glossing over another point, but for the subquery example I would've just done:
SELECT first_name, last_name, email
FROM customer
WHERE customer_id IN
(SELECT DISTINCT customer_id
FROM payment
WHERE extract(month from payment_date) = 4
AND extract(day from payment_date) BETWEEN 10 AND 13
GROUP BY customer_id
HAVING SUM(amount) > 30
ORDER BY SUM(amount) DESC
LIMIT 5);
I suppose I may be glossing over another point, but for the subquery example I would've just done:
SELECT first_name, last_name, email FROM customer WHERE customer_id IN (SELECT DISTINCT customer_id FROM payment WHERE extract(month from payment_date) = 4 AND extract(day from payment_date) BETWEEN 10 AND 13 GROUP BY customer_id HAVING SUM(amount) > 30 ORDER BY SUM(amount) DESC LIMIT 5);