joined:Mar 19, 2009
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?