Denni S Denni S - 7 months ago 12
SQL Question

SQL Server : how to divided the result of sum of total for every costumer id

I have 4 tables like this (you can ignore table B because this problem did not use that table)

enter image description here

I want to show the sum of 'total' for each 'sales_id' from table 'sales_detail'

What I want (the result) is like this :

sales_id | total
S01 | 3
S02 | 2
S03 | 4
S04 | 1
S05 | 2
S05 | 3


I have tried with this query :

select
sum(total)
from
sales_detail
where
sales_id = any (select sales_id
from sales
where customer_id = any (select customer_id
from customer)
)


but the query returns a value if 15 because they are the sum of those rows of data.

I have tried to use "distinct" before sum

and the result is [ 1, 2, 3 ] because those are distinct of those rows of data (not sum of each sales_id)

It's all about subquery

Thanks.

Answer

You are just so far off track that a simple comment won't help. Your query only concerns one table, sales_detail. It has nothing to do with the other two.

And, it is just an aggregation query:

select sd.sales_id, sum(sd.total)
from sales_detail sd
group by sd.sales_id;

This is actually pretty close to what the question itself is asking.

Comments