编撰sql语句实现 group by count

编写sql语句实现 group by count
编写sql语句实现


a表记录的是缴费纪录，其中state_date是date型，记录缴费时间；charge是number型，记录缴费金额，单位元。请按天顺序统计出每天缴费大于100元的缴费记录数。


紧求呀

[解决办法]

SQL code

select convert(varchar(10),state_date,120) as date,    sum(case when charge > 100 then 1 else 0 end) as cntfrom tbgroup by convert(varchar(10),state_date,120)
[解决办法]
SQL code
select state_date from tbgroup by state_datehaving sum(charge) >100order by state_date
[解决办法]
SQL code
select convert(varchar(10),state_date,120) as date,    sum(case when charge > 100 then 1 else 0 end) as cntfrom tbgroup by convert(varchar(10),state_date,120)order by convert(varchar(10),state_date,120)
[解决办法]
SQL code
--刚才是对原表数据存在的日期进行的统计，如果按时间段每天都显示，可以：declare @start datetimedeclare @end datetimeset @start = '2012-07-01'set @end = '2012-07-10';with cte as(    select convert(varchar(10),dateadd(dd,number,@start),120) as date    from master..spt_values    where [type] = 'P' and number between 0 and datediff(dd,@start,@end))select a.date,sum(case when isnull(b.charge,0)>100 then 1 else 0 end) as cntfrom cte a left join tb b on a.date = convert(varchar(10),b.state_date,120)group by a.date