Select counts and avgs from 2 tables

Dev Shed Forums Sponsor:

October 30th, 2012, 02:51 AM

bobert123

Contributing User

Join Date: Mar 2012

Posts: 38

Time spent in forums: 7 h 17 m 29 sec
Reputation Power: 2

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:

Quote:

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.

October 30th, 2012, 10:37 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,355

Time spent in forums: 3 Months 1 Week 2 Days 4 h 25 m
Reputation Power: 4140

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

November 5th, 2012, 09:36 AM

bobert123

Contributing User

Join Date: Mar 2012

Posts: 38

Time spent in forums: 7 h 17 m 29 sec
Reputation Power: 2

Quote:

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.

November 5th, 2012, 10:18 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,355

Time spent in forums: 3 Months 1 Week 2 Days 4 h 25 m
Reputation Power: 4140

Quote:

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

November 6th, 2012, 07:55 AM

bobert123

Contributing User

Join Date: Mar 2012

Posts: 38

Time spent in forums: 7 h 17 m 29 sec
Reputation Power: 2

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.

November 6th, 2012, 10:16 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,355

Time spent in forums: 3 Months 1 Week 2 Days 4 h 25 m
Reputation Power: 4140

Quote:

Originally Posted by bobert123

that query did not do any of these things.

well, then, i guess you get your money back, eh

November 11th, 2012, 05:15 AM

bobert123

Contributing User

Join Date: Mar 2012

Posts: 38

Time spent in forums: 7 h 17 m 29 sec
Reputation Power: 2

anyone have anything helpful here?

November 11th, 2012, 06:17 AM

cafelatte

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Mar 2008

Posts: 1,923

Time spent in forums: 1 Month 5 Days 16 h 21 m 8 sec
Reputation Power: 377

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.

Select counts and avgs from 2 tables

Developer Shed Advertisers and Affiliates