Complex Queries range dates

Question

mySQL dateTime range Query Issue

how get count of proceser in 2017 by same date like 2017-08-07

date         | name      
-----------------------
 2017-08-31   |  amr
-----------------------
 2017-08-05   |  ahmed
----------------- -----
     2018-08-08   | moh

how get 2017-01-01 BETWEEN 2017-12-31
------------------------
count         | date      
-----------------------
 2            |  2017
-----------------------
     1            | 2018

  SELECT  count(*)
    FROM item WHERE  
    date IN 
    ( SELECT  date 
     FROM item WHERE 
     (BETWEEN '2017-03-15' AND '2017-09-31'))

see https://stackoverflow.com/questions/1469689/select-mysql-query-between-date — Duane Lortie, Aug 11 '17 at 19:33
Possible duplicate of [Select mysql query between date?](https://stackoverflow.com/questions/1469689/select-mysql-query-between-date) — Brian J, Aug 11 '17 at 19:47
@AmrEssam How? Update your question to show why it is different. — GrumpyCrouton, Aug 11 '17 at 19:52
@DuaneLortie the appropriate action would be to click "flag" under the post, and go to "a duplicate...", where you would post the link. This flags the question and allows it to be resolved quicker. — GrumpyCrouton, Aug 11 '17 at 19:53
Are you saying you want to GROUP BY and COUNT using different date ranges? There's got to be a duplicate for that hanging around here somewhere. — mickmackusa, Aug 12 '17 at 03:19

score 0 · Answer 1 · answered Aug 11 '17 at 19:55

1, date is a reserved MySQL word. It would be best to name your column something else, but you can use it if you also specify the table name like table.date

2, there is no need for a sub-query. MySQL can do this in one query

SELECT count(`table.name`) as NUMBER, YEAR(`table.date`) as YR FROM item WHERE DATE(`table.date`) BETWEEN '2017-01-01' AND '2017-12-31'

score 0 · Answer 2 · answered Aug 11 '17 at 20:14

0

BETWEEN is kind of a "ternary" operator, of the form x BETWEEN y AND z

You want:

`date` BETWEEN '2017-03-15' AND '2017-09-31'

answered Aug 11 '17 at 20:14

Uueerdo

15,723
1
16
21

`date` BETWEEN '2017-03-15' AND '2017-09-31' and `date` BETWEEN '2018-03-15' AND '2018-09-31' – Amr Essam Aug 11 '17 at 20:40
You'd need "OR" not "and"; a single date cannot be in multiple non-overlapping date ranges any more than 2 can be in 1-3 AND 3-4. – Uueerdo Aug 11 '17 at 20:52

score 0 · Accepted Answer · answered Aug 13 '17 at 08:14

I couldn't find a duplicate for you, but I am sure there is one somewhere.

If you use GROUP BY and the DATE_FORMAT() function, you can COUNT() the occurrences in each group.

SELECT COUNT(*),DATE_FORMAT(`date`,'%Y') FROM `item` GROUP BY DATE_FORMAT(`date`,'%Y');

You can include a WHERE clause before GROUP BY if you need to omit certain ranges of time.

Complex Queries range dates

3 Answers3