0

I've a table like attached.

enter image description here

I want to find the "number of items in a certain month which has maximum entries in the database".

For instance, Jan has 10 entries, Feb has 13 entries, Mar has 8 entries.

I want to find the the number 13 for Feb from the database as it has the max entries. How do I check the time range in the query?

ptamzz
  • 9,235
  • 31
  • 91
  • 147
  • I am not sure exactly what you are looking for. Do you mean how many entries are in a given month? If so try this: http://pastebin.com/VZmfmHsU – 735Tesla Jan 04 '14 at 23:08
  • What exactly are you asking about? How to use `WHERE` on a `DATETIME` column? – lafor Jan 04 '14 at 23:12
  • possible duplicate of [How to use count and group by at the same select statement](http://stackoverflow.com/questions/2722408/how-to-use-count-and-group-by-at-the-same-select-statement) – Protomen Jan 04 '14 at 23:27

2 Answers2

1

You can group all of your realeasedates by month and year to get a count like this:

SELECT MONTH(releasedate) AS month, YEAR(releasedate) as year, count(r_id) AS number
FROM my_table 
GROUP BY YEAR(releasedate), MONTH(releasedate)
ORDER BY YEAR(releasedate), MONTH(releasedate)

This'll give you something like this:

+--------+--------+--------+
| month  |  year  | number |
+--------+--------+--------+
|   1    |  2013  |   13   |
|   2    |  2013  |    8   |

Then you could select the maximum like this:

SELECT MONTH(releasedate) AS month, YEAR(releasedate) as year, count(r_id) AS number
FROM my_table 
GROUP BY YEAR(releasedate), MONTH(releasedate)
ORDER BY count(r_id)
LIMIT 1

Which'll give you:

+--------+--------+--------+
| month  |  year  | number |
+--------+--------+--------+
|   4    |  2013  |   19   |
+--------+--------+--------+

Which'll represent the highest month

Damien Black
  • 5,579
  • 18
  • 24
0
SELECT COUNT(*) FROM `mytable` WHERE releasedate >= DATE '2013-02-01' AND releasedate <= DATE '2013-02-28'

That should work

EDIT:

As suggested by lafor...

WHERE YEAR(releasedate)=2013 AND MONTH(releasedate)=2

should also work

martskins
  • 2,920
  • 4
  • 26
  • 52