如何从表中的字段中获取每月的记录总数。
我有2个表,客户和活动,在活动中有成本(双倍)字段和作为外键客户id,想要显示客户和成本的总和给定的日期。
当日期是2016-01时,我执行了一个查询来求和成本值,在本例中,我希望显示每个月,1月,2月,3月...
select idcliente,
nombre,
sum(costo) as costo
from actividad
inner join cliente
on actividad.cliente_idcliente = cliente.idcliente
where cliente_idcliente = 82
group by fecha=2016-01;这次咨询适用于我所有的客户,而不只是一个
示例:我想显示一个像这样的表http://imgur.com/a/6jYlB
发布于 2016-08-26 09:43:07
看起来您想要一个透视查询结果,其中一年中的12个月是列,每条记录对应于一个给定的客户。尝试执行以下查询:
SELECT idcliente,
SUM(CASE WHEN MONTH(fecha) = 1 THEN costo ELSE 0 END) AS January,
SUM(CASE WHEN MONTH(fecha) = 2 THEN costo ELSE 0 END) AS February,
SUM(CASE WHEN MONTH(fecha) = 3 THEN costo ELSE 0 END) AS March,
SUM(CASE WHEN MONTH(fecha) = 4 THEN costo ELSE 0 END) AS April,
SUM(CASE WHEN MONTH(fecha) = 5 THEN costo ELSE 0 END) AS May,
SUM(CASE WHEN MONTH(fecha) = 6 THEN costo ELSE 0 END) AS June,
SUM(CASE WHEN MONTH(fecha) = 7 THEN costo ELSE 0 END) AS July,
SUM(CASE WHEN MONTH(fecha) = 8 THEN costo ELSE 0 END) AS August,
SUM(CASE WHEN MONTH(fecha) = 9 THEN costo ELSE 0 END) AS September,
SUM(CASE WHEN MONTH(fecha) = 10 THEN costo ELSE 0 END) AS October,
SUM(CASE WHEN MONTH(fecha) = 11 THEN costo ELSE 0 END) AS November,
SUM(CASE WHEN MONTH(fecha) = 12 THEN costo ELSE 0 END) AS December
FROM actividad
INNER JOIN cliente
ON actividad.cliente_idcliente = cliente.idcliente
WHERE cliente_idcliente = 82 AND -- remove this to see monthly summary for all customers
YEAR(fecha) = 2016 -- change this to whatever year you want to see
GROUP BY idcliente发布于 2016-08-26 09:43:56
使用month函数
select
YEAR(fecha) as Anno
MONTH(fecha) as Mes,
min(idcliente),
min(nombre),
sum(costo) as costo
from actividad
inner join cliente on actividad.cliente_idcliente=cliente.idcliente
where cliente_idcliente=82
group by year(fecha), month(fecha)https://stackoverflow.com/questions/39157144
复制相似问题