Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2010
    Posts
    40

    Unanswered: simplying inefficient query

    Hi,
    I have two tables, one with phones and the other with call records. The call records one has columns for the originating and terminating phone whereas the phone table just has info about a phone.

    I'm trying to generate sql to give me

    phone name, numCallsPlaced, numCalledReceived
    =======================================

    Getting just one half is pretty straightforward. For example

    select count(1) as numCallsPlaced,
    (select p.name from phone p where cr.id = p.id) as phonename

    from callrecord cr
    group by phonename


    but to try and get both calls placed and received seems daunting in a single query.

    I have tried doing 'from' against the phone table first and then subselects against the call record but the performance is miserable because for every phone entry it has to requery the large call record table.

    Any thoughts?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    so the callrecord table uses the id column for the originating number, but you neglected to explain how the terminating number is identified, so let's call this column id2
    Code:
    SELECT p.name
         , COALESCE(c_out.calls,0) AS numCallsPlaced
         , COALESCE(c_in.calls,0) AS numCalledReceived
      FROM phone AS p
    LEFT OUTER
      JOIN ( SELECT id
                  , COUNT(*) AS calls
               FROM callrecord
             GROUP 
                 BY id ) AS c_out
        ON c_out.id = p.id
    LEFT OUTER
      JOIN ( SELECT id2
                  , COUNT(*) AS calls
               FROM callrecord
             GROUP 
                 BY id2 ) AS c_in
        ON c_in.id2 = p.id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2010
    Posts
    40
    Thanks a lot. Worked great.

Posting Permissions

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