Raphão Torres Raphão Torres - 5 months ago 8
MySQL Question

SQL Query with 3 Tables using SUM not working

I have 3 tables



tbforecasts



creator[varchar] - date[date] - price[double] - status[int]

tbsector



sector[varchar] - user[varchar]

tbgoal



user[varchar] - goal[double] - month[int] - year[int]


I need a query to sum values in month and year selected and group by sector like this:


sector sum(price) sum(goal)<br/>
production 5000.00 30000.00<br/>
sales 42000.00 150000<br/> <br/>


my actual query don't sum the correct value of goal... more than real..




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;


Answer

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: date, month, year, technically even user (though that one usually doesn't cause too many issues).