waynew Posted July 10, 2008 Share Posted July 10, 2008 Hey guys. Okay... I need to solve a problem that I don't know how to go about approaching. Any help would be appreciated. I have two tables. There names are tenders and jobs. tenders: id job_id tender_price jobs: id reg_date status (won or lost) I have an array of dates. These dates are the distinct month/year from the column reg_date (which is in jobs) of all won ]jobs. These dates are placed on the x axis of a graph (the bottom). Now, what I need to do is to find the sum of all tender_price in ]tenders where the job was won and get the results to sync with the key of my dates array. So that $date[0] will be synched with $price[0]. Does anyone have an idea as to how I can achieve this? Please note that I can not change the structure of the tables as they are apart of a live system with too many dependencies. Link to comment https://forums.phpfreaks.com/topic/114053-complex-sql-to-me-anyway/ Share on other sites More sharing options...
waynew Posted July 10, 2008 Author Share Posted July 10, 2008 Anyone? Link to comment https://forums.phpfreaks.com/topic/114053-complex-sql-to-me-anyway/#findComment-586214 Share on other sites More sharing options...
moselkady Posted July 10, 2008 Share Posted July 10, 2008 You can try this SQL SELECT job_id, reg_date, SUM(tender_price) AS price FROM (SELECT job_id, reg_date, tender_price FROM `tenders` LEFT JOIN jobs ON (jobs.id=tenders.job_id) WHERE jobs.status='won') AS p GROUP BY job_id Link to comment https://forums.phpfreaks.com/topic/114053-complex-sql-to-me-anyway/#findComment-586684 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.