ORDER BY带多个计数和SUM()

Question

我正在努力使下面的语法正确。我尝试过各种工会，但都没有成功。

我需要把这3个总数加起来作为"总数"。数据表非常大，希望找到比第四个子查询更好的方法来获得总数。

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

Answer 1

您的查询(如果我理解其意图(可以简化为：

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

然后您可以按任意列订购。