2

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
  • Do you want to get a total count for aaa and bbb in your code Result:? – oNare Oct 2 '15 at 12:10
  • 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). – IGGt Oct 7 '15 at 7:24
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.)

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.