使用多个计数和SUM（）进行排序

Question

我正在努力得到正确的语法下面。我尝试过不同的工会，但都没有成功。

我需要将这三个总数相加为“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

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

则可以按任何一列进行排序。