SQL 按条件统计各个字段次数

Question

 ID value1  value2  value3  value4  value5 DATETIME_col
A   50  75  0   0   0   2015-10-07 12:27:00.000
A   50  75  0   0   0   2015-10-07 12:29:00.000
A   51  75  0   0   0   2015-10-07 12:39:00.000
B   36  42  29  12  0   2015-10-07 16:58:00.000
B   36  43  30  19  0   2015-10-07 16:57:00.000
B   36  43  30  12  0   2015-10-07 16:56:00.000
C   28  33  29  10  89  2015-10-07 16:54:00.000
C   29  34  29  10  16  2015-10-07 16:55:00.000
C   29  35  29  15  18  2015-10-07 16:56:00.000
C   29  35  30  16  18  2015-10-07 16:57:00.000
D   31  55  34  35  19  2015-10-07 00:10:00.000
D   31  54  34  38  31  2015-10-07 00:11:00.000
D   31  54  34  33  15  2015-10-07 00:12:00.000

条件 在2015-10-07这一天统计 value1>30，value2>30,value3>30,value4<15,value5<40 的结果

结果希望这样显示
ID  vcount1 vcount2 vcount3 vcount4 vcount5 DATE_col
A   3   3   0   3   3   2015-10-07 
B   3   3   0   2   3   2015-10-07 
C   0   4   0   2   3   2015-10-07 
D   3   3   3   0   3   2015-10-07 

Answer 1

with t as 
(

select 'A' as ID ,50 as value1 ,75 as value2 ,0 as value3,0 as value4 ,0 as value5, '2015-10-07 12:27:00.000' as DATETIME_col
union all 
select 'A' ,50,75,0,0,0,'2015-10-07 12:29:00.000'
union all
select 'A' ,51,75,0,0,0,'2015-10-07 12:39:00.000'
union all
select 'B',36,42,29,12,0,'2015-10-07 16:58:00.000'
union all
select 'B',36,43,30,19,0,'2015-10-07 16:57:00.000'
union all
select 'B',36,43,30,12,0,'2015-10-07 16:56:00.000'
union all
select 'C',28,33,29,10,89,'2015-10-07 16:54:00.000'
union all
select 'C',29,34,29,10,16,'2015-10-07 16:55:00.000'
union all
select 'C',29,35,29,15,18,'2015-10-07 16:56:00.000'
union all
select 'C',29,35,30,16,18,'2015-10-07 16:57:00.000'
union all
select 'D',31,55,34,35,19,'2015-10-07 00:10:00.000'
union all
select 'D',31,54,34,38,31,'2015-10-07 00:11:00.000'
union all
select 'D',31,54,34,33,15,'2015-10-07 00:12:00.000'
)
select 
    id
    ,sum(mark_v1) as vcount1
    ,sum(mark_v2) as vcount2 
    ,sum(mark_v3) as vcount3 
    ,sum(mark_v4) as vcount4 
    ,sum(mark_v5) as vcount5
    ,max('2015-10-07 00:11:00.000'::date)
from
    (
        select 
            *
            ,case when value1>30 then 1 else 0 end as mark_v1
            ,case when value2>30 then 1 else 0 end as mark_v2
            ,case when value3>30 then 1 else 0 end as mark_v3
            ,case when value4<15 then 1 else 0 end as mark_v4
            ,case when value5<40 then 1 else 0 end as mark_v5
        from 
            t 
    ) a 
group by id order by id

仅供参考