SitePoint Sponsor |
|
User Tag List
Results 1 to 5 of 5
Thread: select and sum from two tables
-
Nov 30, 2009, 19:26 #1
- Join Date
- Dec 2008
- Posts
- 47
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
select and sum from two tables
hi to all
Ihave a main table with unique "id" with relation in table1 and table2 "main_id". I want to query and ouput the information from main table including the total of debitval from table1 and creditval from table2. Any help would greatly appreciated.
Thanks in advance.
maintable
id payee description reference_no
1 john advance 1
2 dave request 2
table1
id main_id debitval
1 1 5.00
2 1 10.00
3 2 6.00
4 2 7.00
table2
id main_id creditval
1 1 5.00
2 1 10.00
3 1 15.00
4 2 4.00
output table should be like this
id payee description reference_no debitval creditval
1 john advance 1 15.00 30.00
2 dave request 2 13.00 4.00
-
Nov 30, 2009, 19:38 #2
- Join Date
- Aug 2000
- Location
- Philadephia, PA
- Posts
- 20,578
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
Code:SELECT maintable.id, SUM(table1.debitval) AS `debitval`, SUM(table2.creditval) AS `creditval` FROM maintable LEFT OUTER JOIN table1 ON maintable.id = table1.main_id LEFT OUTER JOIN table2 ON maintable.id = table2.main_id GROUP BY maintable.id
Try Improvely, your online marketing dashboard.
→ Conversion tracking, click fraud detection, A/B testing and more
-
Nov 30, 2009, 21:34 #3
- Join Date
- Dec 2008
- Posts
- 47
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
hi Dan Grossman,
Thank for your immediate replied. It work's like a charm.
Best Regards,
Tirso
-
Nov 30, 2009, 22:34 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
sorry, dan, your query produces these results:
Code:id debitval creditval 1 45.0 60.0 2 13.0 8.0
this query --Code:SELECT maintable.id , maintable.payee , maintable.description , maintable.reference_no , d.total_debit , c.total_credit FROM maintable LEFT OUTER JOIN ( SELECT main_id , SUM(debitval) AS total_debit FROM table1 GROUP BY main_id ) AS d ON d.main_id = maintable.id LEFT OUTER JOIN ( SELECT main_id , SUM(creditval) AS total_credit FROM table2 GROUP BY main_id ) AS c ON c.main_id = maintable.id
Code:id payee description reference_no total_debit total_credit 1 john advance 1 15.0 30.0 2 dave request 2 13.0 4.0
-
Dec 1, 2009, 03:55 #5
- Join Date
- Dec 2008
- Posts
- 47
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
hi r937,
I'll notice a while a go that dan solution produce incorrect result. But somehow it was help me a lot.
Thank you r937.
Tirso
Bookmarks