|
|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
|
#1
|
|||
|
|||
|
MySQL joins, Group id and SUM
ok here is an issue im sure many people have. I have 2 queries which i want to optimise into 1. We have built a sponsorship system where by people can donation money to an individual there by increasing their 'sponsor_total' . If the individual is a member of a team the 'team_total' will also increase. Queries below:
SELECT SUM(p.amount) as sponsor_total FROM members m LEFT join donations d on d.member_id = m.id LEFT join payments p on p.id = d.payment_id LEFT join teams t on t.id = m.team_id WHERE m.active = 1 AND m.deleted = 0 AND (d.deleted != 1 or p.amount IS NULL) group by m.id SELECT SUM(p.amount) as team_total from teams t LEFT join members m ON m.team_id = t.id LEFT join donations d on d.member_id = m.id LEFT join payments p on d.payment_id = p.id WHERE m.active = 1 AND m.deleted = 0 AND (d.deleted != 1 or p.amount IS NULL) group by m.team_id The first gets the sponsors total, the second the team total. We need to do this as part of one query. Any help is appreciated |
![]() |
| Viewing: Dev Articles Community Forums > Databases > MySQL Development > MySQL joins, Group id and SUM |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|