How can I group by different years?

I have to group by the different vendors in each given year by calculating the total sales for those years. I could only get the first column.

Output obtained

query='''
    SELECT ven.nombre_vendedor,
    
    SUM(total-costo_envio) AS año2019
 
     FROM itens_pedidos  ip
   
    INNER JOIN pedidos ped ON ped.pedido_id=ip.pedido_id
    INNER JOIN vendedores ven ON ven.vendedor_id=ped.vendedor_id 

    WHERE fecha_compra
    BETWEEN "2019-01-01" AND "2019-31-12"
    GROUP BY ven.nombre_vendedor
    LIMIT 5
    '''

expected output

Answers

To obtain the total sales for each vendor in each year, you need to pivot the data based on the year. You can achieve this by using conditional aggregation with the CASE statement. Here's how you can modify your query to achieve the desired output:

SELECT
    ven.nombre_vendedor,
    SUM(CASE WHEN YEAR(ped.fecha_compra) = 2019 THEN total - costo_envio ELSE 0 END) AS año2019,
    SUM(CASE WHEN YEAR(ped.fecha_compra) = 2020 THEN total - costo_envio ELSE 0 END) AS año2020,
    SUM(CASE WHEN YEAR(ped.fecha_compra) = 2021 THEN total - costo_envio ELSE 0 END) AS año2021
FROM
    itens_pedidos ip
INNER JOIN
    pedidos ped ON ped.pedido_id = ip.pedido_id
INNER JOIN
    vendedores ven ON ven.vendedor_id = ped.vendedor_id
WHERE
    ped.fecha_compra BETWEEN '2019-01-01' AND '2019-12-31'
GROUP BY
    ven.nombre_vendedor
LIMIT 5;

This query uses conditional aggregation with SUM and CASE statements to calculate the total sales for each vendor in each year. It checks the year of the purchase date (ped.fecha_compra) and sums the sales amount (total - costo_envio) accordingly for each vendor. Repeat this process for each year you want to include in your output. Adjust the date range in the WHERE clause for each year as needed.

This query will provide the total sales for each vendor in each specified year. Adjust the years and date range in the WHERE clause as needed to include data for other years.