How do I get the number of rows per day between dates?
I have a table in the following format. I need a mysql query to get the number of rows per day when I give a date between 2008-10-12 and 2008-10-13
Name | KW | KV | I | Date | ------ + -------- + ------ + ------ + -------------------- - + UPS1 | 353.50 | NULL | NULL | 2008-10-12 00:54:36 | UPS1 | 352.50 | NULL | NULL | 2008-10-12 01:54:36 | UPS1 | 351.90 | NULL | NULL | 2008-10-12 02:54:36 | UPS1 | 351.60 | NULL | NULL | 2008-10-12 03:54:36 | UPS1 | 352.20 | NULL | NULL | 2008-10-12 04:54:36 | UPS1 | 352.20 | NULL | NULL | 2008-10-12 05:54:36 | UPS1 | 351.90 | NULL | NULL | 2008-10-12 06:54:36 | UPS1 | 352.50 | NULL | NULL | 2008-10-12 07:54:36 | UPS1 | 352.50 | NULL | NULL | 2008-10-12 08:54:36 | UPS1 | 353.20 | NULL | NULL | 2008-10-12 09:54:36 | UPS1 | 353.50 | NULL | NULL | 2008-10-12 10:54:36 | UPS1 | 352.20 | NULL | NULL | 2008-10-12 11:54:36 | UPS1 | 352.50 | NULL | NULL | 2008-10-12 12:54:36 | UPS1 | 352.20 | NULL | NULL | 2008-10-12 13:54:36 | UPS1 | 353.20 | NULL | NULL | 2008-10-12 14:54:36 | UPS1 | 353.50 | NULL | NULL | 2008-10-12 15:54:36 | UPS1 | 352.90 | NULL | NULL | 2008-10-12 16:54:36 | UPS1 | 352.20 | NULL | NULL | 2008-10-12 17:54:36 | UPS1 | 352.20 | NULL | NULL | 2008-10-12 18:54:36 | UPS1 | 352.90 | NULL | NULL | 2008-10-12 19:54:36 | UPS1 | 352.20 | NULL | NULL | 2008-10-12 20:54:36 | UPS1 | 352.50 | NULL | NULL | 2008-10-12 21:54:36 | UPS1 | 352.90 | NULL | NULL | 2008-10-12 22:54:36 | UPS1 | 353.20 | NULL | NULL | 2008-10-12 23:54:36 | UPS1 | 355.80 | NULL | NULL | 2008-10-13 00:54:36 | UPS1 | 358.40 | NULL | NULL | 2008-10-13 01:54:36 | UPS1 | 358.00 | NULL | NULL | 2008-10-13 02:54:36 | UPS1 | 359.00 | NULL | NULL | 2008-10-13 03:54:36 | UPS1 | 357.70 | NULL | NULL | 2008-10-13 04:54:36 | UPS1 | 357.40 | NULL | NULL | 2008-10-13 05:54:36 | UPS1 | 357.40 | NULL | NULL | 2008-10-13 06:54:36 | UPS1 | 359.00 | NULL | NULL | 2008-10-13 07:54:36 | UPS1 | 357.10 | NULL | NULL | 2008-10-13 08:54:36 | UPS1 | 359.00 | NULL | NULL | 2008-10-13 09:54:36 | UPS1 | 357.70 | NULL | NULL | 2008-10-13 10:54:36 | UPS1 | 357.40 | NULL | NULL | 2008-10-13 11:54:36 | UPS1 | 357.40 | NULL | NULL | 2008-10-13 12:54:36 | UPS1 | 359.00 | NULL | NULL | 2008-10-13 13:54:36 | UPS1 | 357.10 | NULL | NULL | 2008-10-13 14:54:36 | UPS1 | 358.00 | NULL | NULL | 2008-10-13 15:54:36 | UPS1 | 359.30 | NULL | NULL | 2008-10-13 16:54:36 | UPS1 | 357.10 | NULL | NULL | 2008-10-13 17:54:36 | UPS1 | 358.40 | NULL | NULL | 2008-10-13 18:54:36 | UPS1 | 357.70 | NULL | NULL | 2008-10-13 19:54:36 | UPS1 | 359.00 | NULL | NULL | 2008-10-13 20:54:36 | UPS1 | 358.70 | NULL | NULL | 2008-10-13 21:54:36 | UPS1 | 358.70 | NULL | NULL | 2008-10-13 22:54:36 | UPS1 | 358.40 | NULL | NULL | 2008-10-13 23:54:36 |
+1
pallavi
source
to share
5 answers
I modified the query so that the case of the date column matches what you have in the query results.
select Date, count(Date)
from TABLE
where Date >= '2008-10-12' and Date <= '2008-10-13'
group by Date
You will need to change the TABLE in the from clause, but everything else should be correct for your table.
edit:
I have a table with a similar structure and I have changed the date column to be named 'Date'. The following worked for me.
mysql> select Date, count(Date)
from calendar_items
where date between '2008-12-12' and '2008-12-13'
group by Date;
+------------+-------------+
| Date | count(Date) |
+------------+-------------+
| 2008-12-12 | 14 |
| 2008-12-13 | 6 |
+------------+-------------+
2 rows in set (0.00 sec)
mysql>
+8
source to share