How to multiply two columns and add a new column to the database?

Which customer id bought maximum products? (hint : get sales by multiplying product_quantity and price_per_item fields)

SELECT 
    customerid,
    product_quantity * price_per_item as "sales",
    SUM(sales)
FROM questions
GROUP BY customerid

This is how the database looks like

After adding the sales column I am not able to perform the sum operation and getting the following error:

SELECT customerid, product_quantity * price_per_item as "sales", SUM(sales) FROM questions GROUP BY customerid LIMIT 0, 1000
Error Code: 1054. Unknown column 'sales' in 'field list'

Desired output

Solution 1:

The exact error you are seeing is due to the product expression not appearing directly inside an aggregated function. You should have taken the sum of this product directly. You might be fine just using the following LIMIT query:

SELECT customerid
FROM questions
GROUP BY customerid
ORDER BY SUM(product_quantity * price_per_item) DESC
LIMIT 1;

If there could be multiple customers tied for top sales, then use:

SELECT customerid
FROM questions
GROUP BY customerid
HAVING SUM(product_quantity * price_per_item) = (
    SELECT SUM(product_quantity * price_per_item)
    FROM questions
    GROUP BY customerid
    ORDER BY SUM(product_quantity * price_per_item) DESC
    LIMIT 1
);