2

I am very new to SQL and doing my first steps since a few days. But know I have come to a dead spot. It would be very nice if you are willing to help me.

These are the required tables:

Table: Customer_Bonus
Bonus_ID, Customer_ID, Amount
301, 100215, 100
302, 100924, 50
...

Table: Customer_Mapping
Customer_ID, Name, Adress_ID
100215, Doe Industries, 8203351
100924, Al Capone Service Ltd., 849215
...

Table: Customer_Bonus_to_Invoice
Invoice_IDNR, Bonus_ID, Amount
216523, 301, 10
...
244854, 302, 10
...
284111, 301, 10
...
299569, 302, 20
...
316017, 302, 10
...

The question which I am trying to answer: How to get a total sum of all used bonus?

This is what I want
Customer_ID, Name, Bonus_ID, total sum of bonus, sum of used bonus
100215, Doe Industries, 301, 100, 20
100924, Al Capone Service Ltd., 302, 50, 40
...

This is what I have beed trying

SELECT 
Customer_Mapping.Customer_ID
Customer_Mapping.Name,
Customer_Bonus.Bonus_ID,
Customer_Bonus.Amount,
(SELECT 
SUM(Customer_Bonus_to_Invoice.Amount) 
FROM Customer_Bonus_to_Invoice 
WHERE Customer_Bonus_to_Invoice.Bonus_ID = Customer_Bonus.Bonus_ID) 
AS 'sum of used bonus'

FROM 
Customer_Bonus
Customer_Mapping
Customer_Bonus_to_Invoice 

WHERE
Customer_Bonus.Customer_ID = Customer_Mapping.Customer_ID

Is this a job for JOINs?

tho.mass
  • 23
  • 3

2 Answers2

0

Here you go:

SELECT CM.CUSTOMER_ID,
    CM.NAME ,
    CB.Bonus_ID,
    cb.Amount,
    SUM(cbi.Amount)
FROM CUSTOMER_MAPPING CM
INNER JOIN CUSTOMER_BONUS CB
ON CM.CUSTOMER_ID=CB.CUSTOMER_ID
INNER JOIN CUSTOMER_BONUS_TO_INVOICE CBI
ON cbi.Bonus_ID=cb.Bonus_ID
GROUP BY CM.CUSTOMER_ID,
    CM.NAME,
    CB.BONUS_ID,
    cb.Amount;
Cristian_I
  • 1,585
  • 1
  • 12
  • 17
  • Thanks for your amazing quick answer. :) I will need a few minutes to unterstand what this means. – tho.mass Sep 19 '15 at 14:16
  • @tho.mass if you are new at this i suggest a simple but very good tutorial like on [**w3schools**](http://www.w3schools.com/sql/sql_join.asp) – Cristian_I Sep 19 '15 at 14:20
  • It is working fine. What should I say? Thank you very much. :) This is very helpfully and will help me to unterstand and learn. – tho.mass Sep 19 '15 at 15:04
0

Try This:

SELECT t1.Customer_ID,t1.Name,sum(t2.Amount) as    total_sum_of_bonus,sum(t3.Amount) as sum_of_used_bonus FROM Customer_Mapping as t1
LEFT JOIN Customer_Bonus as t2 on t1.Customer_ID=t2.Customer_ID
LEFT JOIN Customer_Bonus_to_Invoice as t3 on t2.Bonus_ID=t3.Bonus_ID
GROUP BY t1.Customer_ID,t1.Name,t3.Bonus_ID
Payer Ahammed
  • 887
  • 1
  • 5
  • 16
  • Also my thank to you. At this point I dont know the difference between the two solutions and a inner and left join. I have a lot to learn. ;) – tho.mass Sep 19 '15 at 15:09