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

Issue

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)

Answered By – P.Salmon

Answer Checked By – Cary Denson (AngularFixing Admin)