I am struggling to get the syntax correct below. I've tried various unions and joins without success.

I need to SUM the 3 totals as 'totcount'. The data table is very large and was hoping to find a better way to obtain totcount than a 4th subquery.

    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

Your query (if I understand the intent) can be simplified to:

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

Then you can order by whichever column.

The content is from StackOverflow which is translated and used in accordance with the CCBY-SA 4.0 license agreement. Original link: ORDER BY with Multiple Counts and SUM()