mysql - 计算选择语句中的位置和位置

嗨，我正试图用mysql编写一个堆积条形图，首先我需要得到正确的查询，并需要一些帮助。
目前我有：

SELECT
COUNT(TargetDate) AS Planned,
COUNT(StartDate) AS Completed,
MONTHNAME(TargetDate) AS `Month`,
StartDate,
AssessmentStatus
FROM AuditCycleAssessments
WHERE (Year(TargetDate) = Year(now()))
GROUP BY MONTHNAME(TargetDate)
ORDER BY Month(TargetDate)

工作正常，看起来screenshot
但是我需要改变第三行，包括它自己的where语句，比如。。。

  (COUNT(StartDate) if AssessmentStatus=3) AS Completed,

所以第一，有人能帮我实现这个目标吗。
谢谢

最佳答案

使用COUNT语句执行条件CASE

SELECT
    COUNT(TargetDate) AS Planned,
    COUNT(CASE WHEN AssessmentStatus=3 THEN StartDate END) AS Completed,
    MONTHNAME(TargetDate) AS `Month`,
    StartDate,
    AssessmentStatus
FROM AuditCycleAssessments
WHERE (Year(TargetDate) = Year(now()))
GROUP BY MONTHNAME(TargetDate)
ORDER BY Month(TargetDate)