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

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!

CodePudding user response：

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)

CodePudding user response：

Subquery returns 4 times 2020-07-07. Then left join match 4 times with prices table. Try it with a distinct:

SELECT `dates`.`date`, COUNT(*)
FROM (
    SELECT distinct 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;