I have this db
I did this
--SELECT a.id --FROM ( SELECT customer.id,customer.firstname,customer.lastname,SUM(quantity) as sum FROM ( orders INNER JOIN Orderitem ON orders.id = orderitem.orderid INNER JOIN product ON product.id = orderitem.productid INNER JOIN Customer ON orders.customerid = customer.id ) GROUP BY customer.id--) as a --HAVING sum = (SELECT MAX(sum) FROM a) ;
I got this error when I uncomment the commented parts:
relation “a” does not exist
I’ve tinkered with it quite a bit, every time I get one error or the other. How do I do this?
If you need only the customer id with the greatest amount you could have it in the following way:
with qt_summed as ( SELECT orders.customerid, SUM(orderitem.quantity) as sum FROM orders INNER JOIN Orderitem ON orders.id = orderitem.orderid GROUP BY orders.customerid order by sum desc limit 1 ) select resource_id as id from qt_summed
You can use
ORDER BY in combination
FETCH FIRST 1 ROW or
LIMIT 1 to get only the row with maximum
SUM(quantity). Further notes:
There may be more than customers with the same (max) number of items purchased. To get them all (instead of an arbitrary one), you can use
The join to
productis not needed (assuming there is valid FK).
You can display all columns from
customerand other aggregate data if you need them.
SELECT customer.id -- customer.*, -- SUM(orderitem.quantity) AS sum FROM orders INNER JOIN orderitem ON orders.id = orderitem.orderid -- INNER JOIN product ON product.id = orderitem.productid INNER JOIN customer ON orders.customerid = customer.id GROUP BY customer.id ORDER BY SUM(orderitem.quantity) DESC FETCH FIRST 1 ROW -- WITH TIES ;
You need to use a window function like
ROW_NUMBER() in the following manner to generate a unique ID ordered over the records by the
SUM() of the quantity field. Then you can filter out everything but the record with the highest quantity
SUM() (SortId = 1).
WITH CTE_Quantity_Summed AS -- CTE to calculate the sum of quantity for each customer ( SELECT customer.id, customer.firstname, customer.lastname, SUM(quantity) as quantitySum FROM orders INNER JOIN Orderitem ON orders.id = orderitem.orderid INNER JOIN product ON product.id = orderitem.productid INNER JOIN Customer ON orders.customerid = customer.id GROUP BY customer.id ), CTE_Quantity_Summed_Sorted AS -- CTE to generate a unique ID per row, sorted by the quantitySum field, descending ( SELECT id, firstname, lastname, quantitySum, ROW_NUMBER() OVER (ORDER BY quantitSum DESC) AS SortId FROM CTE_Quantity_Summed ) SELECT id, firstname, lastname, quantitySum FROM CTE_Quantity_Summed_Sorted WHERE SortId = 1 -- Filters out everything except the row with the highest quantitySum