Home MYSQL COUNT values with dates range select is different than without dates range

Questions

MYSQL COUNT values with dates range select is different than without dates range

May 31, 2022

I have this query to get the count values in range dates (with a unique date filter to one day to view selection detail):

SELECT `dates`.`date`, COUNT(*)
FROM (
    SELECT CURDATE() - INTERVAL (units.mul + (10 * tens.mul) + (100 * hundreds.mul) + (200 * thousands.mul)) DAY AS `date`
    FROM       (SELECT 0 AS mul UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS units
    CROSS JOIN (SELECT 0 AS mul UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS tens
    CROSS JOIN (SELECT 0 AS mul UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS hundreds
    CROSS JOIN (SELECT 0 AS mul UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS thousands
) `dates`
LEFT JOIN `prices` ON (`prices`.`date` = `dates`.`date`)
WHERE `dates`.`date` = '2020-07-07'
GROUP BY `dates`.`date`
ORDER BY `dates`.`date` ASC;

+------------+-----------+
| date       | COUNT(*)  |
+------------+-----------+
| 2020-07-07 |    150840 |
+------------+-----------+
1 row in set (0.06 sec)

But same query only on prices table the result is:

SELECT COUNT(*) FROM `prices` WHERE `date` = '2020-07-07';

+----------+
| COUNT(*) |
+----------+
|    37710 |
+----------+
1 row in set (0.01 sec)

Why first query result is not:

+------------+----------+
| date       | COUNT(*) |
+------------+----------+
| 2020-07-07 |    37710 |
+------------+----------+
1 row in set (0.06 sec)

Thanks!

>Solution :

37710 * 4 = 150840 check your query without the join and you have 4 rows with 2020-07-07 I suspect typo 200 * thousands.mul should be 1000 * thousands.mul

SELECT `dates`.`date`, COUNT(*)
FROM (
    SELECT CURDATE() - INTERVAL (units.mul + (10 * tens.mul) + (100 * hundreds.mul) + (200 * thousands.mul)) DAY AS `date`
    FROM       (SELECT 0 AS mul UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS units
    CROSS JOIN (SELECT 0 AS mul UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS tens
    CROSS JOIN (SELECT 0 AS mul UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS hundreds
    CROSS JOIN (SELECT 0 AS mul UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS thousands
) `dates`
#LEFT JOIN `prices` ON (`prices`.`date` = `dates`.`date`)
WHERE `dates`.`date` = '2020-07-07'
GROUP BY `dates`.`date`
ORDER BY `dates`.`date` ASC;

+------------+----------+
| date       | COUNT(*) |
+------------+----------+
| 2020-07-07 |        4 |
+------------+----------+
1 row in set (0.011 sec)