Flourish PHP Unframework
This is an archived copy of the forum for reference purposes

Mysql count separate dates

posted by darren 8 years ago

hi guys,

ive been struggling to find solution and would really appreciate some help...

i have the following -

+---------------------+---------------------+
| crtdtime            | rstdtime            |
+---------------------+---------------------+
| 2011-11-12 00:00:00 | 2011-11-19 00:00:00 |
| 2011-11-19 00:00:00 | 2011-11-20 00:00:00 |
| 2011-11-13 00:00:00 | 2011-11-19 00:00:00 |
| 2011-11-18 00:00:00 | 2011-11-28 00:00:00 |
| 2011-11-27 00:00:00 | NULL                |
| 2011-11-12 00:00:00 | NULL                |
+---------------------+---------------------+

ive tried various things...

SELECT
	crtdtime,
	count(crtdtime) AS created,
	sum(rstdtime IS NOT NULL) AS restored
FROM
	testing
WHERE
	crtdtime BETWEEN '2011-11-01 00:00:00' AND '2011-11-30 00:00:00'
GROUP BY crtdtime

output

+---------------------+---------+----------+
| crtdtime            | created | restored |
+---------------------+---------+----------+
| 2011-11-12 00:00:00 | 2       | 1        |
| 2011-11-13 00:00:00 | 1       | 1        |
| 2011-11-18 00:00:00 | 1       | 1        |
| 2011-11-19 00:00:00 | 1       | 1        |
| 2011-11-27 00:00:00 | 1       | 0        |
+---------------------+---------+----------+

however what im after is

+---------------------+---------+----------+
| crtdtime            | created | restored |
+---------------------+---------+----------+
| 2011-11-12 00:00:00 | 2       | 0        |
| 2011-11-13 00:00:00 | 1       | 0        |
| 2011-11-18 00:00:00 | 1       | 0        |
| 2011-11-19 00:00:00 | 1       | 2        |
| 2011-11-27 00:00:00 | 1       | 0        |
+---------------------+---------+----------+

that way it shows how many restored on the crtdtime...

im sure its something simple im overlooking :(

many thanks

Remember that BETWEET is like > and <, it doesn't include equal values ( >= or <= )

posted by jmtucu 8 years ago