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