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

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

CodePudding user response：

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

CodePudding user response：

You cannot use an alias created in a select clause in the same select clause, because the expressions have no order. This means, that product_quantity * price_per_item as "sales" is not necessarily executed before SUM(sales), so the DBMS tells you that sales is unknown.

You don't need the alias anyway, because with one result row per customer, what amount other than the sum would you want to show?

SELECT
  customerid,
  SUM(product_quantity * price_per_item) AS sales
FROM questions
GROUP BY customerid
ORDER BY customerid;

This doesn't get you the top customer, though. But your query didn't either :-) This is just an explanation what's wrong in your original query.

Here are two options on how to build up on this to get the top customer(s):

Option 1 with a subquery (a CTE here):

WITH list AS
(
  SELECT
    customerid,
    SUM(product_quantity * price_per_item) AS sales
  FROM questions
  GROUP BY customerid
)
SELECT *
FROM list
WHERE sales = (SELECT MAX(sales) FROM list);

Option 2 with an analytic function:

SELECT customerid, sales
FROM
(
  SELECT
    customerid,
    SUM(product_quantity * price_per_item) AS sales,
    MAX(SUM(product_quantity * price_per_item)) OVER() AS max_sales
  FROM questions
  GROUP BY customerid
) with_max
WHERE sales = max_sales;