我正在努力得到正确的语法下面。我尝试过不同的工会，但都没有成功。
我需要将这三个总数相加为“totcount”。数据表非常大，希望找到一种比第4个子查询更好的方法来获取totcount。
SELECT
location.*,data.status,
(SELECT COUNT(data.id) FROM data WHERE data.locid=location.locid AND data.status='NEW') AS newcount,
(SELECT COUNT(data.id) FROM data WHERE data.locid=location.locid AND data.status='IN-PROGRESS') AS ipcount,
(SELECT COUNT(data.id) FROM data WHERE data.locid=location.locid AND data.status='COMPLATED') AS compcount
FROM TP_locations
LEFT JOIN data ON data.locid=location.locid AND data.status IN('NEW','IN-PROGRESS','COMPLETED')
WHERE data.status IS NOT NULL
GROUP BY location.locid
ORDER BY totcount
您的查询（如果我理解其意图）可以简化为：
SELECT
location.*,
data.status, --this is meaningless, it will give you a random one of the 3 possible values
COUNT(IF(data.status='NEW',1,null) AS newcount,
COUNT(IF(data.status='IN-PROGRESS',1,null) AS ipcount,
COUNT(IF(data.status='COMPLATED',1,null) AS compcount,
COUNT(1) AS totcount
FROM TP_locations
JOIN data ON data.locid=location.locid AND data.status IN('NEW','IN-PROGRESS','COMPLETED')
GROUP BY location.locid
ORDER BY totcount
则可以按任何一列进行排序。
