我有一个包含列名课程和状态的表,如下所示
Course Status
---------------------------
Math Complete
Math Complete
Math Complete
Science Incomplete
Science Complete
Science Complete
我想要一个MySQL查询谁给以下结果
Course % Status_complete
--------------------------
Math 100%
Science 33.3%
SELECT Course,
SUM(Status = 'Complete') / COUNT(*) * 1.0 AS STATUS_COMPLETE
FROM TableName
GROUP BY Course
- <
- SQLFiddle演示/gh>
- SQLFiddle Demo (with % symbol and ROUND())
使用:-
SELECT Course,
SUM(Status = 'Complete') / COUNT(*) * 100.0 as PercentageComplete,
SUM(Status = 'Incomplete') / COUNT(*) * 100.0 as PercentageIncomplete
FROM CourseTable
GROUP BY Course
输出: -
COURSE PERCENTAGECOMPLETE PERCENTAGEINCOMPLETE
Math 100 0
Science 66.66667 33.33333