Displaying the id of the customer with the greatest amount of purchased products

I have this db

https://pastebin.com/WWRxApWn

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 WITH TIES.

• The join to product is not needed (assuming there is valid FK).

• You can display all columns from customer and other aggregate data if you need them.

Query:

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