#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    59
    Rep Power
    3

    Select counts and avgs from 2 tables


    HI I have 2 tables, 1 for successful attempts and 1 with failed attempts (successtable and failedtable). I'm trying to write a query which gets some avgs for a day using both tables grouped by (of all things) area codes of 2 numbers. specifically:
    1. the avg of successful attempts (ie. success/total)
    2. the avg time of the successful attempts (not including failed in the avg)
    3. avg prep time for all attempts (including failed)

    structure of both tables is
    | tid | primnum | secondnum | ttime | tpreptime | tdate |

    to just get the avg time of successful attempts, I was using something like this:
    Select left(primnum,3) as ph1, left(secondnum,3) as ph2, avg(ttime) as avgtime, count(*) as success
    from successtable where tdate=20121029
    group by ph1, ph2
    and that seemed to work okay. but i'm not having any luck with combining results from the second table. also, I have to factor in that some people have only successful attempts and some have only failed.

    any suggestions are appreciated, but please do not tell me to change the structure of the tables, since I do not have access to do that.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    Code:
    SELECT 'success' AS result_type
         , LEFT(primnum,3) AS ph1
         , LEFT(secondnum,3) AS ph2
         , AVG(ttime) AS avgtime
         , COUNT(*) AS success
      FROM successtable 
     WHERE tdate = 20121029
    GROUP 
        BY ph1
         , ph2 
    UNION ALL
    SELECT 'fail' 
         , LEFT(primnum,3) 
         , LEFT(secondnum,3) 
         , AVG(ttime)
         , COUNT(*) 
      FROM failtable 
     WHERE tdate = 20121029
    GROUP 
        BY ph1
         , ph2
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    59
    Rep Power
    3
    Originally Posted by r937
    Code:
    SELECT 'success' AS result_type
         , LEFT(primnum,3) AS ph1
         , LEFT(secondnum,3) AS ph2
         , AVG(ttime) AS avgtime
         , COUNT(*) AS success
      FROM successtable 
     WHERE tdate = 20121029
    GROUP 
        BY ph1
         , ph2 
    UNION ALL
    SELECT 'fail' 
         , LEFT(primnum,3) 
         , LEFT(secondnum,3) 
         , AVG(ttime)
         , COUNT(*) 
      FROM failtable 
     WHERE tdate = 20121029
    GROUP 
        BY ph1
         , ph2
    that does not do any of the things i was trying to do.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    Originally Posted by bobert123
    that does not do any of the things i was trying to do.
    i call bs

    there are two SELECTs in that union query, and the first one is exactly the same as the one you posted that you said works well

    so unless you have more information on what you actually wanted, i can't help you any further
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    59
    Rep Power
    3
    as i wrote initially:

    1. the avg of successful attempts (ie. success/total)
    2. the avg time of the successful attempts (not including failed in the avg)
    3. avg prep time for all attempts (including failed)

    that query did not do any of these things.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    Originally Posted by bobert123
    that query did not do any of these things.
    well, then, i guess you get your money back, eh

    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    59
    Rep Power
    3
    anyone have anything helpful here?
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,927
    Rep Power
    378
    It's good to show your own efforts - as you have done - because it means we can see that you're trying to solve the problem for yourself.

    More useful to us however are
    - the DDLs used to construct the table(s)
    - a small but representative data set (in the form of a concise set of INSERT statements)
    - the desired result
    - the result of SELECT VERSION();

    All of the above should be wrapped up in [ code ][ /code ] tags (minus the spaces).

    Unfortunately, the Stickies at the top of this forum do a poor job of communicating this.

IMN logo majestic logo threadwatch logo seochat tools logo