Venta mensual de MySQL de los últimos 12 meses, incluidos los meses sin venta

zero monthcalendar

Question

SELECT DATE_FORMAT(date, "%b") AS month, SUM(total_price) as total
FROM cart
WHERE date <= NOW()
and date >= Date_add(Now(),interval - 12 month)
GROUP BY DATE_FORMAT(date, "%m-%Y")

Esta consulta muestra el resultado solo para el mes existente. Necesito todas las ventas de 12 meses.

Salida :

"month" "total"  
--------------  
"Jun"   "22"
"Aug"   "30"
"Oct"   "19"
"Nov"   "123"
"Dec"   "410"

Salida requerida :

"month" "total"  
--------------
"Jan"   "0"
"Feb"   "0"
"Mar"   "0"
"Apr"   "0"
"May"   "0"
"Jun"   "22"
"Jul"   "0"
"Aug"   "30"
"Sep"   "0"
"Oct"   "19"
"Nov"   "123"
"Dec"   "410"

Answer 1

Considere la siguiente tabla

mysql> select * from cart ;
+------+------------+-------------+
| id   | date       | total_price |
+------+------------+-------------+
|    1 | 2014-01-01 |          10 |
|    2 | 2014-01-20 |          20 |
|    3 | 2014-02-03 |          30 |
|    4 | 2014-02-28 |          40 |
|    5 | 2014-06-01 |          50 |
|    6 | 2014-06-13 |          24 |
|    7 | 2014-12-12 |          45 |
|    8 | 2014-12-18 |          10 |
+------+------------+-------------+

Ahora, según la lógica, está mirando hacia atrás un año y december aparecerá dos veces en el resultado, es decir, dec 2013 and dec 2014 y si necesitamos tener un recuento separado para ellos, entonces podemos usar la siguiente técnica de generación de rango dinámico de fechas MySql Single Tabla, seleccione los últimos 7 días e incluya filas vacías

t1.month,
t1.md,
coalesce(SUM(t1.amount+t2.amount), 0) AS total
from
(
  select DATE_FORMAT(a.Date,"%b") as month,
  DATE_FORMAT(a.Date, "%m-%Y") as md,
  ''0'' as  amount
  from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
  ) a
  where a.Date <= NOW() and a.Date >= Date_add(Now(),interval - 12 month)
  group by md
)t1
left join
(
  SELECT DATE_FORMAT(date, "%b") AS month, SUM(total_price) as amount ,DATE_FORMAT(date, "%m-%Y") as md
  FROM cart
  where Date <= NOW() and Date >= Date_add(Now(),interval - 12 month)
  GROUP BY md
)t2
on t2.md = t1.md 
group by t1.md
order by t1.md
;

La salida será

+-------+---------+-------+
| month | md      | total |
+-------+---------+-------+
| Jan   | 01-2014 |    30 |
| Feb   | 02-2014 |    70 |
| Mar   | 03-2014 |     0 |
| Apr   | 04-2014 |     0 |
| May   | 05-2014 |     0 |
| Jun   | 06-2014 |    74 |
| Jul   | 07-2014 |     0 |
| Aug   | 08-2014 |     0 |
| Sep   | 09-2014 |     0 |
| Oct   | 10-2014 |     0 |
| Nov   | 11-2014 |     0 |
| Dec   | 12-2013 |     0 |
| Dec   | 12-2014 |    55 |
+-------+---------+-------+
13 rows in set (0.00 sec)

Y si no le importa el caso anterior, es decir, dec 2014 and dec 2013

Luego simplemente cambie el group by en la parte de fecha dinámica como

where a.Date <= NOW() and a.Date >= Date_add(Now(),interval - 12 month)
  group by month

y grupo final por como group by t1.month

Answer 2

Gracias por la sugerencia de @pankaj, aquí lo resolví a través de esta consulta ...

SELECT 
    SUM(IF(month = ''Jan'', total, 0)) AS ''Jan'',
    SUM(IF(month = ''Feb'', total, 0)) AS ''Feb'',
    SUM(IF(month = ''Mar'', total, 0)) AS ''Mar'',
    SUM(IF(month = ''Apr'', total, 0)) AS ''Apr'',
    SUM(IF(month = ''May'', total, 0)) AS ''May'',
    SUM(IF(month = ''Jun'', total, 0)) AS ''Jun'',
    SUM(IF(month = ''Jul'', total, 0)) AS ''Jul'',
    SUM(IF(month = ''Aug'', total, 0)) AS ''Aug'',
    SUM(IF(month = ''Sep'', total, 0)) AS ''Sep'',
    SUM(IF(month = ''Oct'', total, 0)) AS ''Oct'',
    SUM(IF(month = ''Nov'', total, 0)) AS ''Nov'',
    SUM(IF(month = ''Dec'', total, 0)) AS ''Dec'',
    SUM(total) AS total_yearly
    FROM (
SELECT DATE_FORMAT(date, "%b") AS month, SUM(total_price) as total
FROM cart
WHERE date <= NOW() and date >= Date_add(Now(),interval - 12 month)
GROUP BY DATE_FORMAT(date, "%m-%Y")) as sub