homepage Welcome to WebmasterWorld Guest from 54.197.94.241
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Query Help - Averaging, etc
CyBerAliEn




msg:4125722
 2:44 pm on May 2, 2010 (gmt 0)

Database Information
I have a database setup that basically contains customers, vehicles, and complaints.

Customers = people who own a vehicle (just name, address, ssn, etc)
Vehicles = relational table; relates "customers" to "products" (ie: what "vehicle" a customer owns), where products are individual vehicles (brand & model, year, etc).
Complaints = customer has issue/problem with their vehicle and it goes in for repair

Complaints relates to Vehicles by "VehicleID" (in both tables; primary in Vehicles, foreign in Complaints)

Customers relates to Vehicles by "SSN" (in both tables; primary in Customers, foreign in Complaints)

So to get "customer" information from a "complaint", you basically have to go:
Complaints -> Vehicles -> Customer

I am running mySQL version 5.1.44-community on my laptop (development work).


PROBLEM
I have this query:
SELECT DISTINCT cus.Name,
(SELECT COUNT(comx.CID) FROM tblcomplaint AS comx,tblcustomer AS cusx,tblvehicle AS vx WHERE ((vx.VehicleID=comx.VehicleID) AND (vx.SSN=cusx.SSN) AND (cus.SSN=cusx.SSN))) AS 'COUNT'
FROM tblcustomer AS cus,tblvehicle AS v,tblcomplaint AS com WHERE ((com.VehicleID=v.VehicleID) AND (v.SSN=cus.SSN));


It produces an output table such as the following:
Name | COUNT
Andrew Hieber | 2
Nick Hulsy | 2
Zack Ames | 1
Edward Juarez | 2
Stewart Griffin | 1
Tom Cruise | 2


Want I want to do... is to take this "results table" and acquire the AVERAGE and SAMPLE STANDARD DEVIATION of the column "count". In a simple case, I know you can just do:
SELECT AVG(someCol) FROM someTable;

But in this case, the query is more advanced. I tried putting an "AVG" around the whole query, around just the subquery that produces the COUNT, etc... but these all result in errors.


Can anyone guide me as to how to accomplish performing these operations on the column of this resulting table?

Thanks appreciated!

 

whoisgregg




msg:4126830
 5:39 pm on May 4, 2010 (gmt 0)

There's surely a more elegant way, but you can get the average by adding another column definition:

SELECT DISTINCT cus.Name,
(SELECT COUNT(comx.CID) FROM tblcomplaint AS comx,tblcustomer AS cusx,tblvehicle AS vx WHERE ((vx.VehicleID=comx.VehicleID) AND (vx.SSN=cusx.SSN) AND (cus.SSN=cusx.SSN))) AS 'COUNT',
(SELECT AVG(comx.CID) FROM tblcomplaint AS comx,tblcustomer AS cusx,tblvehicle AS vx WHERE ((vx.VehicleID=comx.VehicleID) AND (vx.SSN=cusx.SSN) AND (cus.SSN=cusx.SSN))) AS 'AVG'
...


As you can imagine, this means it would need to perform the subquery a second time.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved