How to Join with Sum and Group By

Navigation
0

I'm trying to do a SQL query using Join between two tables. The first table is a table that contains monthly savings values for a given project. Each time someone registers a project on the site, a PHP routine generates 12 values, one in the current month and another 11 months in the future. Because the website has a facility where the plant manager can choose the time period that wants to show the savings, I have created a table containing only the months in the period the manager wants to view. I would like to create a SQL query that based on the calendar table, would serve as the basis for the table containing the actual savings and dates of the implemented actions.

In other words, for each month of the calendar table, I would like to get the sum of the action table of its month (add all actions in that month of the action table) from the Saving_Mensal column and return all months of the defined interval in the calendar table. In the months of the calendar table that there are no savings, I would like it to return 0.

The calendar table has the following structure: The table with the 12 stock values has the following structure:

The search I'm trying to perform is: SELECT date_format(calendar.Data, '%Y-%m') as Mes, sum(action_plan_detail.Saving_Mensal) AS Saving FROM calendar LEFT JOIN action_plan_detail ON action_plan_detail.Saving_Mensal = calendar.Saving_Mensal GROUP BY Mes

and the return is null in every month

Can anyone help me figure out which search is right here?

The result I would like in the end would be 

Mes     | Saving_Mensal
--------|--------------
01-2018 | 20000
02-2018 | 20000
03-2018 | 20000
04-2018 | 20000
05-2018 | 20000
06-2018 | 0      * não existe nenhum lançamento na tabela de açoes com valores neste mês
07-2018 | 0
08-2018 | 0
09-2018 | 20000
10-2018 | 20000
11-2018 | 20000
12-2018 | 20000
    
asked by anonymous 24.01.2018 / 22:27

1 answer

0

Fernando, I believe that the join should be done in the calendar.Data field and not in action_plan_detail.Saving_Mensal. 

SELECT date_format(calendar.Data, '%Y-%m') AS Mes, 
SUM(action_plan_detail.Saving_Mensal) AS Saving FROM calendar 
LEFT JOIN action_plan_detail ON calendar.Data = action_plan_detail.Data 
GROUP BY Mes
    
25.01.2018 / 22:34
Hover in selector: before SpringBoot finds no dependency on BOOT-INF / lib folder