Geek asks and answers

Convert and rotate in mysql question

Hope someone can show me the light.

I’ve been trying for a long time to get the result of some sales by weeks, days and years in one request without any chance.

I found a syntax that can help me, but not working in mysql.

    TRANSFORM 
   Sum(Cantidad) AS Ventas 
SELECT 
   Producto, Cantidad 
FROM 
   Pedidos 
WHERE 
   Fecha Between #01-01-1998# And #12-31-1998# 
GROUP BY 
   Producto 
ORDER BY 
   Producto 
PIVOT 
   DatePart("m", Fecha)

My real request is this:

select sum(orders.final_price) AS total
     , orders.id_comercial
     , vendedores.nombre
from orders
   , vendedores
WHERE orders.id_comercial = vendedores.id
  AND DATE(orders.date_purchased) = DATE(NOW())
GROUP BY orders.id_comercial

I will really take care of some ideas, maybe at the end of the night here in Spain;)

The result of my query is the following:

total         id_comercial         nombre
740.83        24        AITOR ANGULO
2069.37       29        FERNANDO I
482.05        32        JOSE ANDRES
961.32        33        ALBERTO FERNANDEZ -CANTABRIA-
908.66        34        GONZALO
49.54         38        LUIS LOPEZ
4082.42       39        JULEN DEL CURA
1512.87       43        ALBERTO POBLACION
1268.91       44        MASSIMO
3269.08       45        JOSE RAMON BURGA
56.49         47        EMPRESA
2791.65       48        MARCO MORILLO
1445.57       61        TINO
869.73        63        SONIA
1052.02       381       DAVID ARIAS

Really thnx to everyone.

+3
source share
1 answer

For example, this will give you a day, a 7-day and a year-totals total

select sum(case when DATE(orders.date_purchased) = DATE(NOW()) then orders.final_price end) AS TotalToday
     , sum(case when DATE(orders.date_purchased) >= DATE(NOW() -7) then orders.final_price end) AS TotalLast7Days
     , sum(case when YEAR(orders.date_purchased) = YEAR(NOW()) then orders.final_price end) AS TotalYearToDate
     , orders.id_comercial
     , vendedores.nombre
from orders
   , vendedores
WHERE orders.id_comercial = vendedores.id
  AND orders.date_purchased >= date(concat(year(now()),'-01-01'))
GROUP BY orders.id_comercial, vendedores.nombre

FYI date(concat(year(now()),'-01-01'))gives you the first day of the current year

But if you want a pivot table, then

select sum(case when MONTH(orders.date_purchased) = 1 then orders.final_price end) AS Jan
     , sum(case when MONTH(orders.date_purchased) = 2 then orders.final_price end) AS Feb
      ....
     , sum(case when MONTH(orders.date_purchased) = 12 then orders.final_price end) AS Dec
     , orders.id_comercial
     , vendedores.nombre
from orders
   , vendedores
WHERE orders.id_comercial = vendedores.id
  AND orders.date_purchased >= date(concat(year(now()),'-01-01'))
GROUP BY orders.id_comercial, vendedores.nombre

MySQL ( , ) TRANSFORM, .

+1

More articles:


All Articles