I have 3 tables
sector sum(price) sum(goal)<br/>
production 5000.00 30000.00<br/>
sales 42000.00 150000<br/> <br/>
SELECT s.sector, sum(m.goal), sum(f.price) AS price
FROM tbsector AS s
JOIN tbgoals AS m ON m.user = s.user
JOIN tbforecasts AS f ON f.creator = s.user
WHERE m.month = 6 AND m.year = 2016
AND month(f.date) = 6 AND year(f.date) = 2016
GROUP BY s.sector;
They need to be summed separately, remember that without the grouping and sums, queries similar to that will match up every
tbgoals record with every
tbforecasts record, based on same user|creator; so if a "sector" has 2 goals and 3 forecasts, you will get (and sum) 6 results for that sector.
There are a number of ways to solve this, the most common are to: put one of the sums in a subquery, and then join that to the other table(s) involve to get the other sum; or put both sums into separate subqueries and then join those to the common table.
From the information you've given so far, it is not readily apparent which (if either) would be most accommodating; as how the data relates (and not just it's structure) can matter.
The second approach I mentioned will look something like this:
SELECT s.sector, totalGoals, totalPrices FROM tbsector AS s LEFT JOIN (SELECT user, sum(goal) AS totalGoals FROM tbgoals WHERE `month` = 6 AND `year` = 2016 GROUP BY user ) AS m ON m.user = s.user LEFT JOIN (SELECT creator, sum(price) AS totalPrices FROM tbforecasts WHERE month(f.`date`) = 6 AND year(f.`date`) = 2016 ) AS f ON f.creator = s.user GROUP BY s.sector;
I am guessing this is the more appropriate solution from the original query's WHERE conditions (note that I moved them into the relevant subqueries).
Sidenote: If it is not too late in the design process, I would recommend renaming some of your table's fields to not be the same as MySQL keywords/reserved words; like:
year, technically even
user (though that one usually doesn't cause too many issues).