Hi

I have the following table:

date number called answered
2006-08-10 9051231234 0
2006-08-10 9051231235 1
2006-08-11 9051231231 0
2006-08-11 9051231211 0
2006-08-06 9051231222 1
2006-08-06 9051231233 0
2006-08-06 9051231233 0

need to get the report on how many calls have been placed for particular day and how many were answered, something like this:

2006-08-06 3 1
2006-08-10 2 0
2006-08-11 2 1

How to do this in one query or without creating an interim table.
Thanks.

Can you post the URL or a copy of the assignment exactly as it came from the teacher? There are usually some subtle quirks in how the assignments are worded that will influcence how you need to solve the problem to get a good grade.

It would also help to know exactly what hardware/software they'll test this on, since that can influence the choices we make too.

-PatP

pat, you cynic

905 area code is just outside toronto, and i happen to know there are no sql classes going on right now

this has to be a real world problem, not a homework assignment!!

xirurg, would you please show the query that you've managed to work out by yourself so far? use correct table and column names, please

This should be relatively simple, viz. grouping by date, counting to find the second column, and summing to get the third one.

SQL classes? Yes I think I'd need them :-). Yes it's the real issue.
Software is mySQL. Here are the fields which matter (there are more in the table but they are irrelevant for this task):
+-------------+--------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------------------+-------+
| calldate | datetime | | MUL | 0000-00-00 00:00:00 | |
| dcontext | varchar(80) | | | | |
| disposition | varchar(45) | | | | |

dcontext has values outbond or incoming - I'm interesting in outbound only. Disposition has values ANSWERED, NO ANSWER, FAILED. I'm interesed in ANSWERED only.
Report should look like:
calldate, number of outbound calls, number of answered calls.

I can get those numbers by using 2 separate queries:
select left(calldate,11),count(*) from cdr where dcontext<>"incoming" group by left(calldate,11);
select left(calldate,11),count(*) from cdr where disposition="ANSWERED" and dcontext<>"incoming" group by left(calldate,11);

but then I have to combine results either in temp table or inside programming code (Perl in this case) so I was thinking there is a way to do it in one query. I tried to use UNION SELECT and CUBE grouping, CASE as well - no luck

Xirurg

Try this:Instead of "LEFT(calldate,11)" also try "CAST(calldate AS date)" -- should be better in terms of performance, in case mySQL supports this.

Thanks Peter