I have 3 tables.
The first one is called Months and it is conformed in the following way:
The second table is called orders and is formed as follows:
Where the fields in the red box are the ones that matter.
The third table is called treatments and is formed as follows:
What I need to do is first take the months of the months table, count the orders per month of the referred orders table of the date_reg field and then take the values of the treatments table, according to the id of the treatment field of the orders table to obtain the total sum per month.
Currently I can get the number of orders per month, but not the sum of the values per month. I have tried all the forms that have occurred to me and found in the different forums, but it does not work for me.
What I currently have is the following:
SELECT YEAR(pedidos.fecha_pedido) AS ANO, nombre_mes, ifnull(count(pedidos.id),0) AS cantidad,
(SELECT SUM(tratamientos.valor) FROM tratamientos INNER JOIN pedidos P ON P.lab = 4 INNER JOIN tratamientos t ON t.id = P.tratamiento) AS TOTAL
FROM meses
LEFT JOIN pedidos ON meses.id = MONTH(pedidos.fecha_pedido) AND YEAR(pedidos.fecha_pedido) = 2018
GROUP BY meses.id
What I did was try to join the tables in some way and what I get is the following:
Where clearly, the total column is wrong and that's where I need to list the sum of the values of the orders placed and show zero where the amount is zero. If you lack code or explain something, I will gladly add or explain it. I appreciate any help or guidance that you can give me. thank you very much.