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.