
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