SELECT
CASE WHEN speed<=30 THEN 'low' WHEN speed<=60 THEN 'mid' ELSE 'hi' END[type]
,COUNT(1)[count]
FROM
tablename
GROUP BY
CASE WHEN speed<=30 THEN 'low' WHEN speed<=60 THEN 'mid' ELSE 'hi' END]
CASE WHEN speed<=30 THEN 'low' WHEN speed<=60 THEN 'mid' ELSE 'hi' END[type]
,COUNT(1)[count]
FROM
tablename
GROUP BY
CASE WHEN speed<=30 THEN 'low' WHEN speed<=60 THEN 'mid' ELSE 'hi' END]
union all
select 'lmid' as type,sum(case when speed between 30 and 60 then 1 else 0 end) as count from tb
union all
select 'hi' as type,sum(case when speed>60 then 1 else 0 end) as count from tb
SELECT Convert(datetime, '12:00:00', 114), 35.1 UNION ALL
SELECT Convert(datetime, '12:00:01', 114), 34.2 UNION ALL
SELECT Convert(datetime, '12:00:05', 114), 40.5 UNION ALL
SELECT Convert(datetime, '12:10:03', 114), 20.5 UNION ALL
SELECT Convert(datetime, '13:00:01', 114), 80.3 UNION ALL
SELECT Convert(datetime, '13:00:03', 114), 81.7 UNION ALL
SELECT Convert(datetime, '13:00:04', 114), 82.1
)
,t1 AS (
SELECT DateDiff(minute,
Convert(datetime, '00:00:00', 114),
[time]
) / 10 AS tenMinutes,
speed
FROM test
)
,t2 AS (
SELECT tenMinutes,
AVG(speed) AS AvgSpeed
FROM t1
GROUP BY tenMinutes
)
,t3 AS (
SELECT tenMinutes,
CASE WHEN AvgSpeed <= 30.0 THEN
'low'
WHEN AvgSpeed <= 60.0 THEN
'mid'
ELSE
'hi'
END AS [type]
FROM t2
)
SELECT [type],
Count(*) AS [count]
FROM t3
GROUP BY [type]
type count
---- -----------
hi 1
low 1
mid 1