CODEWITHSUNDEEP
mysqlsqldate
Raja Dhasan
Raja Dhasan

Reputation: 593

Select Date range in between two dates and get count

I have a table(name vcar)

id  | createddatetime    | 
--------------------------
1   |2016-08-20 17:23:53 |
2   |2016-08-20 17:23:53 |
3   |2016-08-20 17:23:53 |
4   |2016-08-20 17:23:53 |
4   |2016-08-20 17:23:53 |
4   |2016-08-21 17:23:53 |
3   |2016-08-21 17:23:53 |
1   |2016-08-21 17:23:53 |
1   |2016-08-21 17:23:53 |
2   |2016-08-24 17:23:53 |
3   |2016-08-24 17:23:53 |
4   |2016-08-24 17:23:53 |
2   |2016-08-29 17:23:53 |
2   |2016-08-29 17:23:53 |
3   |2016-08-29 17:23:53 |
4   |2016-08-29 17:23:53 |
4   |2016-08-30 17:23:53 |
2   |2016-08-30 17:23:53 |
3   |2016-08-30 17:23:53 |
4   |2016-08-30 17:23:53 |

I try to get the count of id's in a particular date range using 

SELECT
 count(CAST(createddatetime AS DATE)),
 CAST(createddatetime AS DATE),
 DAYNAME(CAST(createddatetime AS DATE)) 
FROM vcar 
WHERE
CAST(createddatetime AS DATE)>=date('2016-08-20') AND 
CAST(createddatetime AS DATE)<=date('2016-08-35') 
GROUP BY CAST(createddatetime AS DATE);

this gives me the o/p , but I also need to show dates which has zero hits on a particular day.How can this be done?

O/p I am getting

-------------------------------------- ------------------------------- ----------------------------------------

     count(CAST(createddatetime AS DATE)) | CAST(createddatetime AS DATE) | DAYNAME(CAST(createddatetime AS DATE)) |

        -------------------------------------- ------------------------------- ----------------------------------------
                                            9 | 2016-08-20                    | Saturday                               |
                                           36 | 2016-08-23                    | Tuesday                                |
                                            4 | 2016-08-24                    | Wednesday                              |
        -------------------------------------- ------------------------------- ----------------------------------------

What I need

-------------------------------------- ------------------------------- ----------------------------------------
 count(CAST(createddatetime AS DATE)) | CAST(createddatetime AS DATE) | DAYNAME(CAST(createddatetime AS DATE)) |
-------------------------------------- ------------------------------- ----------------------------------------
                                    9 | 2016-08-20                    | Saturday                               |
                                    0 | 2016-08-21                    | Sunday                                 |
                                    0 | 2016-08-22                    | Monday                                 |
                                   36 | 2016-08-23                    | Tuesday                                |
                                    4 | 2016-08-24                    | Wednesday                              |
                                    9 | 2016-08-25                    | Thursday                               |
                                    0 | 2016-08-26                    | Friday                                 |                        
-------------------------------------- ------------------------------- ----------------------------------------

Upvotes: 1

Views: 1538

Answers (1)

1000111
1000111

Reputation: 13519

A calendar table would make this much easier. Since you don't have any then the following query would do the job.

SELECT 
dateTable.day,
DAYNAME(dateTable.day) AS dayName,
COALESCE(yourQuery.total,0) AS cnt
FROM 
(
    SELECT ADDDATE('2016-08-20', INTERVAL @i:=@i+1 DAY) AS DAY
    FROM (
    SELECT a.a
    FROM (SELECT 0 AS a 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 a
    CROSS JOIN (SELECT 0 AS a 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 b
    CROSS JOIN (SELECT 0 AS a 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 c
    ) a
    JOIN (SELECT @i := -1) r1
    WHERE 
    @i < DATEDIFF('2016-08-31', '2016-08-20')
) AS dateTable
LEFT JOIN
(
    SELECT
        DATE(createddatetime) date,
        count(id) As total
    FROM vcar 
    WHERE DATE(createddatetime) BETWEEN '2016-08-20' AND '2016-08-31'
    GROUP BY date
) AS yourQuery
ON dateTable.day = yourQuery.date 
ORDER BY dateTable.day

Note:

The query inside the dateTable alias will generate dates from 2016-08-20 to 2016-08-31.

Check this

Upvotes: 3

Related Questions