Take count based on rage of date SQL

Question

I have below mentioned sql query which gives me simple count as on that date or if i use where and and condition for date it gives me consolidated count between the date.

select count(*) from A where date(date_1) = '2017-06-01';

I want count for a range of date for separate count for each date.

select count(*) from A where date(date_1) >= '2017-06-01' and date(date_1)<='2017-06-30';

Desired output:

Date          Count

2017-06-01    25
2017-06-02    20
2017-06-03    15
-             -
-             -
2017-06-30    10

Eray Balkanli · Answer 1 · 2018-02-08T14:12:26.657

2

All you need is to use group by clause like:

SELECT date(date_1),Count(*) as [Count]
FROM A
WHERE date(date_1) >= '2017-06-01' and date(date_1)<='2017-06-30'
GROUP BY date(date_1)

edited Feb 08 '18 at 14:12

answered Feb 08 '18 at 14:11

Eray Balkanli

7,752
11
48
82

This won't indicate dates with missing data. – collapsar Feb 08 '18 at 14:11
@ErayBalkanli Thanks...:) – Vector JX Feb 08 '18 at 14:15
Could use `between` to shorten `where` clause. – G. Grothendieck Feb 08 '18 at 15:59

ngj · Accepted Answer · 2018-02-08T14:13:49.630

2

Something like the following might work:

SELECT DATE(date_1), COUNT(*)
FROM A
GROUP BY DATE(date_1)

COUNT, being an aggregate function, is often combined with the GROUP BY clause in order to get aggregate results per group (in this case, counts per date).

WHERE clauses can of course be added as desired, right before the GROUP BY clause.

edited Feb 08 '18 at 14:13

answered Feb 08 '18 at 14:11

ngj

883
7
17

Dates missing in the table will be missing from the output – collapsar Feb 08 '18 at 14:12
True, but for now it seems unclear whether this is a requirement. – ngj Feb 08 '18 at 14:17

score 1 · Answer 3 · answered Feb 08 '18 at 14:38

The following solution also reports days with no noted incidents. The example is devised for the month of February:

    SELECT (DATE'2018-01-31' + INTERVAL x.n DAY) dd
         , COALESCE(sqt.c, 0)                    incidents
      FROM (select 1 n union all select 2 n union all select 3 n union all select 4 n union all select 5 n union all select 6 n union all select 7 n union all select 8 n union all select 9 n union all select 10 n union all select 11 n union all select 12 n union all select 13 n union all select 14 n union all select 15 n union all select 16 n union all select 17 n union all select 18 n union all select 19 n union all select 20 n union all select 21 n union all select 22 n union all select 23 n union all select 24 n union all select 25 n union all select 26 n union all select 27 n union all select 28 n) x
 LEFT JOIN (
                SELECT date1    d
                     , count(*) c
                  FROM t
              GROUP BY date1
           ) sqt
        ON sqt.d = (DATE'2018-01-31' + INTERVAL x.n DAY)
         ;

The standalone demo can be viewed here.

The sequence generator has been shamelessly adopted from this SO answer.

Take count based on rage of date SQL

3 Answers3