What is the total amount each customer spent at the restaurant? Not able to see different customer_id.. shows only one same id for all amounts


select customer_id ,(numbers*price) as amount 
from (select *,  count(s.product_id) as numbers
      from sales s group by product_id) as sub1 
inner join
( select s.product_id, price 
  from menu m inner join sales s on m.product_id = s.product_id 
  group by product_id) as sub2 on sub1.product_id = sub2.product_id; 

sample data :

INSERT INTO sales ("customer_id", "order_date", "product_id") 
VALUES ('A', '2021-01-01', '1'),('A', '2021-01-01', '2'),('A', '2021-01-07', '2'),('A', '2021-01-10', '3'),('A', '2021-01-11', '3'),('A', '2021-01-11', '3'),('B', '2021-01-01', '2'),('B', '2021-01-02', '2'),('B', '2021-01-04', '1'),('B', '2021-01-11', '1'),('B', '2021-01-16', '3'),('B', '2021-02-01', '3'),('C', '2021-01-01', '3'),('C', '2021-01-01', '3'),('C', '2021-01-07', '3'); 

INSERT INTO menu ("product_id", "product_name", "price") 
VALUES ('1', 'sushi', '10'),('2', 'curry', '15'),('3', 'ramen', '12'); 


customer_id   amount
A              30
A              60
A              96

whereas i want it to show A,B,C as customer ids as the amount is calculated correctly for them. Its showing A as customer_id for all three as first three ids in sales table are for customer A. I tried group by with customer id as well.. it shows only one result, i.e. A and 30.


1 answer

  • answered 2021-05-15 18:53 eshirvana

    based on the provided data from comments,

    i need output with amount spent by A, B and C

    select  customer_id , sum(price)
    from sales s
    join menu m on s.product_id = m.product_id
    group by customer_id
    order by customer_id