SUM and COUNT of existing records or not in related tables

1

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.

    
asked by maha1982 09.12.2018 в 04:30
source

1 answer

1

Maybe something like that will help you. Remove the correlated query because I did not see much sense. I changed IFNULL by COALESCE (personal preference) and the filters of the correlated query I put them in a sub query to which the LEFT JOIN will be applied.

SELECT
    YEAR(a.fecha_pedido) AS 'anio',
    COALESCE(COUNT(a.id_pedido), 0) as 'cantidad',
    meses.nombre_mes,
    COALESCE(SUM(a.valor), 0) as 'total'
FROM 
    meses
    LEFT JOIN (
        SELECT 
            pedidos.id as 'id_pedido',
            pedidos.fecha_pedido,
            tratamientos.valor,
            pedidos.lab
        FROM 
            pedidos 
            JOIN tratamientos
                ON tratamientos.id = pedidos.tratamiento
        WHERE 
            pedidos.lab = 4
            AND YEAR(pedidos.fecha_pedido) = 2018
    ) a 
        ON meses.id = MONTH(a.fecha_pedido)
GROUP BY meses.id;

By running this query in several tables with test data, you gave a result of the form.

+------+------------+----------+-------+
| anio | nombre_mes | cantidad | total |
+------+------------+----------+-------+
| 2018 | Enero      |        5 | 20000 |
| 2018 | Febrero    |        4 | 16000 |
| 2018 | Marzo      |        5 | 20000 |
| 2018 | Abril      |        1 |  4000 |
| NULL | Mayo       |        0 |     0 |
| NULL | Junio      |        0 |     0 |
| NULL | Julio      |        0 |     0 |
| NULL | Agosto     |        0 |     0 |
| NULL | Septiembre |        0 |     0 |
| NULL | Octubre    |        0 |     0 |
| 2018 | Noviembre  |        2 |  8000 |
| NULL | Diciembre  |        0 |     0 |
+------+------------+----------+-------+

I thought something like this:

SELECT
    -- donde operas con los datos
    -- de la tabla principal y donde se
    -- maneja la presentación de los datos.
FROM 
    etc
    LEFT JOIN (
        -- tabla principal que funcionará para servirse
        -- de sus datos.
    ) etc
    
answered by 09.12.2018 / 19:24
source