Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2005
    Posts
    5

    Unanswered: Help with SQL query is required please

    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.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    This should be relatively simple, viz. grouping by date, counting to find the second column, and summing to get the third one.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    Nov 2005
    Posts
    5
    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

    Quote Originally Posted by r937
    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

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by xirurg
    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.
    Try this:
    Code:
    SELECT LEFT(calldate,11) AS calldate,
           COUNT(*) AS number_of_outbound_calls,
           SUM(CASE disposition WHEN 'ANSWERED' THEN 1 ELSE 0 END)
           AS  number_of_answered_calls
    FROM   cdr
    WHERE  dcontext<>'incoming'
    GROUP BY LEFT(calldate,11)
    Instead of "LEFT(calldate,11)" also try "CAST(calldate AS date)" -- should be better in terms of performance, in case mySQL supports this.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  7. #7
    Join Date
    Nov 2005
    Posts
    5
    Thanks Peter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •