I'm having some difficulties finding the good query I need, looking for help. After multiple JOIN and a lot of WHERE, I have this columns and values :
+---------+----------+--------+-------+
| id_test | quantity | price | fee |
+---------+----------+--------+-------+
| "1-3" | 1 | 33.52 | 29.00 |
| "1-5" | 1 | 33.52 | 29.00 |
| "1-6" | 1 | 33.52 | 29.00 |
| "1-8" | 1 | 86.49 | 29.00 |
| "19-1" | 1 | 176.54 | 29.00 |
| "19-4" | 1 | 176.54 | 29.00 |
| "19-5" | 4 | 176.54 | 29.00 |
| "19-6" | 1 | 199.47 | 29.00 |
| "19-6" | 1 | 176.54 | 29.00 |
| "20-10" | 2 | 72.67 | 29.00 |
| "20-11" | 2 | 18.95 | 29.00 |
| "20-9" | 1 | 22.13 | 29.00 |
+---------+----------+--------+-------+
Each id_test object as a quantity, a price and a fee. I would like to SUM everything so to have a global price : (quantity*price)+fee The thing is, and that's where I'm stuck, the fee has to be added only once by id_test. Yet, I have here two id_test "19-6".
If I SUM everything I have 1827.67 (price*quantity) + 348 (fee).
Since I have two "19-6", I need 1827.67 (price*quantity) + 319 (fee).
How would you do in SQL ?
Thanks
You can make use of Common Table Expressions (CTE)
Assuming your data in "data" table:
with total_fee as (
select distinct id_test, fee from data
),
total_price as (
select distinct id_test, sum(quantity) quantity, sum(price) price
from data
group by id_test
)
select sum(b.price*quantity) quantity, sum(a.fee) fee
from total_fee a
left join total_price b
on a.id_test = b.id_test