MySQL GROUP_CONCAT Query, is there a better way

group-concatenationMySQL

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

Best Answer

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.)

Related Question