Question 21126 in Mysql Question posted in
The official MySQL documentation can be found here.

Output the total amount spent by the customer and the item on which he/she spent the most – Mysql

There is a table like this, how can I get customer_id, amount_spent, top_item out of it?

amount_spent the amount spent on all items by this customer

top_item, which displays the name of the item for which the customer has spent the most money

Table

I have tried the following query, however I cannot output the top_1 item with it


select customer_id, sum(item_number * item_price) as amount_spent_1m 
from temp
group by customer_id

Check the demo here.

Tags:

2

Answers


  1. 0

    You can achieve it as below :

    select customer_id,  sum(item_number * item_price) as amount_spent_1m, 
item_name as top_item_1m
from temp
group by customer_id, item_name
order by amount_spent_1m desc;

    It gives me the following result :

    enter image description here

    Login or Signup to reply.
  2. 0

    I am sure there is a way to do this with one less step but my brain is not seeing it right now –

    SELECT customer_id, amount_spent, item_name AS top_item
FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY item_total DESC) rn,
    SUM(item_total) OVER (PARTITION BY customer_id) amount_spent
  FROM (
    SELECT customer_id, item_id, item_name, SUM(item_price * item_number) item_total
    FROM table1
    GROUP BY customer_id, item_id, item_name
  ) t1
) t2
WHERE rn = 1

    db<>fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search