MySQL GROUP_CONCAT Query, is there a better way?

Question

I have the below Data / Query. Is this really the most efficient way of doing this? As it seems like a lot of work to get the result.

I've tried reducing it / combining the parts but this is the only way I can get it to work.

(n.b. The actual table has a lot more entries over multiple days - hence the WHERE filters at the end)

CREATE TABLE test.`actest` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `LS` varchar(25) NOT NULL,
 `ET` datetime NOT NULL,
 `STATUS` int(11) unsigned DEFAULT NULL,
 KEY `ix_id` (`id`)
) ENGINE=InnoDB;
INSERT INTO test.actest (`LS`,`ET`,`STATUS`)
VALUES ('aaa',NOW(),'123'),('aaa',NOW(),'123'),('aaa',NOW(),'123'),('aaa',NOW(),'456'),
('aaa',NOW(),'456'),('aaa',NOW(),'789'),('bbb',NOW(),'123'),('bbb',NOW(),'123'),('bbb',NOW(),'456');
SELECT DATE(`ET`) AS ED, `LS`, GROUP_CONCAT(DISTINCT(cnt2) ORDER BY cnt2 SEPARATOR ', ') AS STATUSES
FROM 
 (
 SELECT `ET`, `STATUS`, `LS`,CONCAT(`STATUS`,' (',cnt,') ') AS cnt2
 FROM 
 (
 SELECT `LS`,`ET`,`STATUS`,COUNT(`STATUS`) AS cnt
 FROM actest
 GROUP BY `LS`, `STATUS`
 ) a
 ) b
WHERE `ET`
 BETWEEN CONCAT(DATE_SUB(CURDATE(), INTERVAL 0 DAY),' 00:00:00') 
 AND CONCAT(DATE_SUB(CURDATE(), INTERVAL 0 DAY),' 23:59:59')
AND `STATUS` > 0 
GROUP BY `ED`,`LS`;
Result:
ED_________| LS__| STATUSES__________________
2015年10月01日 | aaa | 123 (3) , 456 (2) , 789 (1) 
2015年10月01日 | bbb | 123 (2) , 456 (1) 

---

Also, is there an easy way to get a TOTAL Count for each LS field, e.g

aaa | 6
bbb | 3

• oNare: Do you want to get a total count for aaa and bbb in your code Result:?
• IGGt: sorry for not responding, I've been ill. Yes, Ideally I would like TOTAL as the TOTAL of ALL statuses for each LS (e.g. aaa = 3+2+1), as well as the GROUP_CONCAT showing the total of each individual status (as above).

Answer 1

Don't you need to group the inner stuff by ED as well?

I think the inner 2 selects can be combined:

( SELECT `ET`, `STATUS`, `LS`,
 CONCAT(`STATUS`, ' (', COUNT(*), ') ') AS cnt2
 FROM actest
 GROUP BY `ED`, `LS`, `STATUS`
) as b

To get the "total count", add an expression that has a correlated subquery:

( SELECT COUNT(*) FROM actest WHERE LS = b.LS AND DATE(ET) = b.ED ) AS TotalCount,

Putting it all together... (I'm not sure I got it right.)

SELECT DATE(`ET`) AS ED,
 `LS`, 
 ( SELECT COUNT(*)
 FROM actest
 WHERE LS = b.LS
 AND DATE(ET) = b.ED ) AS TotalCount,
 GROUP_CONCAT(DISTINCT(cnt2) ORDER BY cnt2 SEPARATOR ', ') AS STATUSES
 FROM 
 ( SELECT DATE(ET) AS ED,
 `STATUS`,
 `LS`,
 CONCAT(`STATUS`, ' (', COUNT(*), ') ') AS cnt2
 FROM actest
 GROUP BY DATE(ET), `LS`, `STATUS` 
 ) as b
 WHERE ET >= CURDATE() - INTERVAL 7 DAY
 AND ET > CURDATE() - INTERVAL 0 DAY
 AND `STATUS` > 0
 GROUP BY `ED`, `LS`;

(I simplified the date expression, and showed how to get the 7 days leading up to midnight this morning.) (I suspect I mangled some details; check carefully.)

Answer 2

In fact, you don't even have to use a subquery to get the total for each day and LS.

The innermost subquery is already getting a count of the records; we can just use that.

SELECT DATE(`ET`) AS ED
 ,`LS`
 ,GROUP_CONCAT(DISTINCT(CONCAT(`STATUS`,' (',cnt,') ')) ORDER BY `STATUS` SEPARATOR ', ') AS STATUSES
 ,SUM(cnt) as TOTAL_COUNT
FROM 
 (
 SELECT `LS`,`ET`,`STATUS`,COUNT(`STATUS`) AS cnt
 FROM actest
 GROUP BY `LS`, `ET`, `STATUS`
 ) b
WHERE `ET`
 BETWEEN CONCAT(DATE_SUB(CURDATE(), INTERVAL 0 DAY),' 00:00:00')
 AND CONCAT(DATE_SUB(CURDATE(), INTERVAL 0 DAY),' 23:59:59')
AND `STATUS` > 0 
GROUP BY `ED`,`LS`;

This is very similarly to Rick James' solution. We both condensed the two subqueries in the original into one. However, he concatenated the COUNT directly into the status string. I went the other way; I got the count in the subquery, and then built the status string inside the GROUP_CONCAT statement.

Tested out at this db-fiddle link - your original query is there as well, so you can compare.

You might also want to see if the DISTINCT in the GROUP_CONCAT is actually necessary - I strongly suspect it's not.

Note: it's possible that I'm taking advantage of some change in MySQL since October 2015 - I used 5.7 and db-fiddle.com, as you can see if you like.