How to Join with Sum and Group By

Asked

Viewed 520 times

0

I’m trying to do an SQL search using Join between two tables. The first table is a table containing 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 forward. Since the website has a feature where the factory manager can choose the period to show savings, I created a table containing only the months in the period the manager wants to view. I would like to create an SQL query that based on the calendar table, serves as the basis for the table containing the savings and actual dates of the implemented actions.

In other words, for each month of the calendar table, I would like to get the sum of the share table of the respective month (sum all actions in that month of the share table) from the monthly Saving_column and return all months of the interval defined in the calendar table. In the calendar table months that there are no Savings, I would like to return the value 0.

The calendar table has the following structure
inserir a descrição da imagem aqui The table with the 12 stock values has the following structure
inserir a descrição da imagem aqui

The research I’m trying to execute 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 zero in all months inserir a descrição da imagem aqui

Can someone help me determine which correct research to be done 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
mysql join group-by sum

  • I understood nothing, I recommend trying to simplify your question and focus more on the problem, not the context.

  • For each existing month in the calendar table, sum all equivalent months in the stock table (add monthly Saving_for all records with equivalent month of the calendar table) and return the total monthly Saving_value in that month, for all existing months in the calendar table.

1 answer

0


Fernando, I believe that Join should be done in the Legend field. Date and not in action_plan_detail.Saving_monthly.

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

  • Thank you for your help Carlos! Doing the Join of the table as you said worked out! Only shows the months present in the calendar table... however the values did not migrate or were not summed... returned all as zero.

  • Ok Fernando. The dates are always the first day of the month: 01/01/18, 01/02/18 ... 01/12/2018? If not, you can replace the direct comparison of the date field with LEFT JOIN YEAR(Legend.Data) = YEAR(action_plan_detail.Data ) AND MONTH(Legend.Data) = MONTH(action_plan_detail.Data)

  • No, dates may vary by day, but whether it’s the 1st or the 15th, consider the whole month.

  • Forehead like this: SELECT date_format(calendar.Data, '%Y-%m') AS Mes, 
SUM(action_plan_detail.Saving_Mensal) AS Saving FROM calendar LEFT JOIN YEAR(calendar.Data) = YEAR(action_plan_detail.Data ) AND MONTH(calendar.Data) = MONTH(action_plan_detail.Data) GROUP BY Mes

  • It didn’t work because Select ta by selecting two columns in two different tables... I must select everything first from the Calendar table or from the Actions table?

  • SELECT DATE_FORMAT(action_plan_detail.Data, '%Y-%m') AS Mes, SUM(action_plan_detail.Saving_monthly) AS Saving FROM action_plan_detail LEFT JOIN Calendar ON YEAR(action_plan_detail.Data) = YEAR(Data) AND MONTH(action_plan_detail.Data) = YEAR(Legend.Data) GROUP BY Mes ---- Performing the query so it returns legal, but the missing months in the actions table are not returned with value 0...

  • Yes, because your main table has changed. Instead of Calendar, it was action... What was the error of the select I sent you?? These two columns in two different tables..

  • I noticed a mistake in the appointment I sent you. Run like this: 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 YEAR(calendar.Data) = YEAR(action_plan_detail.Data ) AND MONTH(calendar.Data) = MONTH(action_plan_detail.Data) GROUP BY Mes

  • Carlos! Sensational now worked, the values that do not exist returned null! I can ask just one last piece of advice: all this research must be done according to a third field, the sector... For example in the action table there is a field called "Area"... in the site there is a field where the user chooses the area to filter.. would be using the WHERE?

  • Cool @Fernandoferrarifernandes! That’s it! Try to use alias for your tables. It’s cooler! ;)

  • You’re right, I’ll do it! Look thank you so much for the support you saw, you really helped me a lot!

Show 6 more comments

Browser other questions tagged mysql join group-by sum

You are not signed in. Login or sign up in order to post.