# 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

Query

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

Output

``````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.

``````select  customer_id , sum(price)