Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – 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
    '''
nombre_vendedor año_2019
Ana Duarte 2154261.6
Daniel Siqueira 2020503.2
Millena Pereira 2054682.4
Nadia Oliveira 2296986.4

expected output

nombre_vendedor año2019 año2020 año2021
Ana Duarte 2154261.6 5054009.6 7154261.6
Daniel Siqueira 2020503.2 5254009.6 9054261.6
Millena Pereira 2054682.4 3854261.6 1854261.6
Nadia Oliveira 2296986.4 2154261.6 4554261.6
Tags:

2

Answers


  1. 0

    the total sales for each vendor are grouped by year, you need to use conditional aggregation. your query to include separate sums for each year using CASE statements inside the SUM function.

    query='''
    SELECT 
        ven.nombre_vendedor,
        SUM(CASE WHEN fecha_compra BETWEEN "2019-01-01" AND "2019-12-31" THEN total - costo_envio ELSE 0 END) AS año2019,
        SUM(CASE WHEN fecha_compra BETWEEN "2020-01-01" AND "2020-12-31" THEN total - costo_envio ELSE 0 END) AS año2020,
        SUM(CASE WHEN fecha_compra BETWEEN "2021-01-01" AND "2021-12-31" 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 
        fecha_compra BETWEEN "2019-01-01" AND "2021-12-31"
    GROUP BY 
        ven.nombre_vendedor
    LIMIT 5
'''

    above query will calculate the total sales for each vendor for each year separately using conditional aggregation based on the fecha_compra (purchase date). you can to adjust the date ranges in the CASE statements to match the desired year intervals.

    Login or Signup to reply.
  2. 0 
    select ip.nombre_vendedor, a.year, b.year, c.year
from items_pedidos  ip,(
    select pedido_id, SUM(total-costo_envio) AS year
    from pedidos , vendedores 
    where ven.vendedor_id=pedidos.vendedor_id and ip.pedido_id=pedido_id ip and YEAR(fecha_compra)='2019'
    group by pedido_id) a,
    (select pedido_id, SUM(total-costo_envio) AS year
    from pedidos , vendedores 
    where ven.vendedor_id=pedidos.vendedor_id and ip.pedido_id=pedido_id ip YEAR(fecha_compra)='2020'
    group by pedido_id) b,
    (select pedido_id, SUM(total-costo_envio) AS year
    from pedidos , vendedores 
    where ven.vendedor_id=pedidos.vendedor_id and ip.pedido_id=pedido_id ip YEAR(fecha_compra)='2021'
    group by pedido_id) c
where ip.pedido_id=a.pedido_id AND ip.pedido_id=b.pedido_id AND ip.pedido_id=c.pedido_id
limit 5
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search