按照多个计数和SUM()排序

问题

我正在努力纠正下面的语法。我尝试过各种联合和连接，但都没有成功。

我需要将这3个总数加起来作为“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

然后你就可以按照任意一列进行排序了。

内容来源于StackOverflow, 遵循 CCBY-SA 4.0 许可协议进行翻译与使用。原文链接：ORDER BY with Multiple Counts and SUM()