Welcome to WebmasterWorld Guest from 54.221.30.139

Forum Moderators: open

Message Too Old, No Replies

Query Help - Averaging, etc

     

CyBerAliEn

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

5+ Year Member



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

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

WebmasterWorld Senior Member whoisgregg is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month