求电话号码为13812345678的当年中的月，季消费金额和次数。求电话号码为13812345678的当年的消费次数。
信息表：
id   消费金额   联系电话   日期

求电话号码为13812345678的当年中的月，季消费金额和次数。

求电话号码为13812345678的当年的消费次数。

------解决方案--------------------
select sum(消费金额),
第一季度=sum(case where month(日期)= '01 ' or month(日期)= '02 ' or month(日期)= '03 ' then 消费金额 else 0 end),
第二季度=sum(case where month(日期)= '04 ' or month(日期)= '05 ' or month(日期)= '06 ' then 消费金额 else 0 end),
第三季度=sum(case where month(日期)= '07 ' or month(日期)= '08 ' or month(日期)= '09 ' then 消费金额 else 0 end),
第四季度=sum(case where month(日期)= '10 ' or month(日期)= '11 ' or month(日期)= '12 ' then 消费金额 else 0 end),
次数=count(1)
from 信息表
where 联系电话= '13812345678 '
group by convert(char(7),日期,21)
------解决方案--------------------
试试看
select substring(convert(varchar(20),日期,120),6,2) 月份,
sum(消费金额) 月消费,count(1) 次数
from tablename
where 联系电话= '13812345678 ' and year(日期)=year(getdate())
group by substring(convert(varchar(20),日期,120),6,2)
------解决方案--------------------
case 消费金额 when ' ' then 0 ??
消费金额字段还有空字符串？？


将

case 消费金额 when ' ' then 0 when null then 0 else 消费金额 end

改成

case when 消费金额 is NULL then 0 else 消费金额 end case)

试一下~

------解决方案--------------------
---------------------------------第一题--月统计------------------------
select convert(varchar(7), getdate(),102) as 月份
, sum(isnull(消费金额,0)) as 消费
, count(1) as 次数
from comein
where 联系电话= '13812345678 ' and year(日期) = '2007 '
union all
select '汇总 ' as 月份
, sum(isnull(消费金额,0)) as 消费
, count(1) as 次数
from comein
where 联系电话= '13812345678 ' and year(日期) = '2007 '

------解决方案--------------------
---------------------------------第一题--月统计------------------------
(select convert(varchar(7), 日期,102) as 月份
, sum(isnull(消费金额,0)) as 消费
, count(1) as 次数
from comein
where 联系电话= '13812345678 ' and year(日期) = '2007 '
group by convert(varchar(7), 日期,102))
union all
select '汇总 ' as 月份
, sum(isnull(消费金额,0)) as 消费
, count(1) as 次数
from comein
where 联系电话= '13812345678 ' and year(日期) = '2007 '

--------------------------------第二题---季度统计-----------------------------
select year(日期) as 年份
, cast(sum(isnull(消费金额,0)) as varchar(15)) as 一季, ' ' as 二季, ' ' as 三季, ' ' as 四季
, count(1) as 消费次数
from comein
where 联系电话= '13812345678 ' and year(日期) = '2007 ' and month(日期) between 1 and 3
group by year(日期)
union all
select year(日期) as 年份
, ' ' as 一季, cast(sum(isnull(消费金额,0)) as varchar(15)) as 二季, ' ' as 三季, ' ' as 四季
, count(1) as 消费次数
from comein
where 联系电话= '13812345678 ' and year(日期) = '2007 ' and month(日期) between 4 and 6
group by year(日期)
union all
select year(日期) as 年份
, ' ' as 一季, ' ' as 二季, cast(sum(isnull(消费金额,0)) as varchar(15)) as 三季, ' ' as 四季
, count(1) as 消费次数
from comein
where 联系电话= '13812345678 ' and year(日期) = '2007 ' and month(日期) between 7 and 9
group by year(日期)
union all
select year(日期) as 年份
, ' ' as 一季, ' ' as 二季, ' ' as 三季, cast(sum(isnull(消费金额,0)) as varchar(15)