MySql Join with Sum

Question

Hello, I have a table called RESULTS with this structure :

resultid,winner,type

And a table called TICKETS with this structure :

resultid,ticketid,bet,sum_won,status

And I want to show each row from table RESULTS and for each result I want to calculate the totalBet and Sum_won using the values from table TICKETS

I tried to make some joins,some sums,but I cant get what I want.

SELECT *,COALESCE(SUM(tickets.bet),0) AS totalbets,
      COALESCE(SUM(tickets.sum_won),0) AS totalwins 
FROM `results` NATURAL JOIN `tickets` 
WHERE tickets.status<>0 
GROUP BY resultid

Please give me some advice.

I want to display something like this

RESULT WINNER TOTALBETS TOTALWINS
1       2        431       222
2       3         0         0
3       1         23        0
4       1         324       111

Answer 1

Use:

   SELECT r.*,
          COALESCE(x.totalbet, 0) AS totalbet,
          COALESCE(x.totalwins, 0) AS totalwins
     FROM RESULTS r
LEFT JOIN (SELECT t.resultid,
                  SUM(t.bet) AS totalbet,
                  SUM(t.sum_won) AS totalwins
             FROM TICKETS t
            WHERE t.status != 0
         GROUP BY t.resultid) x ON x.resultid = r.resultid

I don't care for the NATURAL JOIN syntax, preferring to be explicit about how to JOIN/link tables together.

Answer 2

SELECT *, COALESCE(SUM(tickets.bet),0) AS totalbets,
      COALESCE(SUM(tickets.sum_won),0) AS totalwins 
FROM `results` NATURAL JOIN `tickets` 
WHERE tickets.status<>0 
GROUP BY resultid

Try to replace the first * with resultid. If this helps, then add more columns to SELECT and add them to GROUP BY at the same time.