求助SQL查询统计问题
成绩 时间100 2011-3-2 17:40:00
100 2011-3-2 17:40:00
99 2011-3-2 17:41:00
99 2011-3-2 17:40:00
95 2011-3-1 17:40:00
75 2011-2-28 17:40:00
73 2011-2-28 17:40:00
70 2011-3-1 17:40:00
70 2011-2-28 17:40:00
60 2011-3-2 17:40:00
50 2011-3-1 17:40:00
6 2011-3-2 17:40:00
6 2011-3-3 0:30:00
3 2011-3-3 0:24:00
请问各位用SQL语句想得到以下结果应怎么写
日期 合格 不合格 合格率
2011-3-1 5 3 62.5%
2011-3-2 6 5 54.5%
我用以下语句怎么都得不到想要的结果,就是不知道错误出在哪里
SELECT CONVERT(VARCHAR(30), 时间, 111),
COUNT(CASE WHEN cast(成绩 AS int) >= 90 THEN 1 ELSE 0 END) AS 合格,
COUNT(CASE WHEN cast(成绩 AS int) < 90 THEN 1 ELSE 0 END) AS 不合格,
COUNT(CASE WHEN cast(成绩 AS int) >= 90 THEN 1 ELSE 0 END)
/ COUNT(CASE WHEN cast(成绩 AS int) < 90 THEN 1 ELSE 0 END) * 100 AS 合格率
FROM mlks_data_backup
GROUP BY CONVERT(VARCHAR(30), 时间, 111)