AVG() through multiple tables?

[kernelgpf]

I'm trying to get the average of several columns [all with the name of SIR] in several different tables, and display the result. My code is giving me a "please check the manual depending on your MySQL version.." error. Here's my code- any help would be appreciated.

 

$query=mysql_query("select AVG(barenas.SIR,caves.SIR,tanks.SIR,training_areas.SIR,vet_clinics.SIR,grooming_centers.SIR,shops.SIR) from barenas,caves,tanks,training_areas,vet_clinics,grooming_centers,shops");
$row=mysql_fetch_array($query);
echo "<li>your district's average safety inspection rating is <b>".$row['AVG(SIR)']."</b> [this is based upon individual building SIRs in your district]";

[neel_basu]

Whats the Error message its showing.

[Barand]

A couple of things

 

1 ) you can't specify multiple cols in and AVG(). Need to be AVG(barenas.SIR) , AVG(caves.SIR) ,... etc.

 

2 ) if you specify a join like this

 

from barenas,caves,tanks,training_areas,vet_clinics,grooming_centers,shops

 

you join every record in each table with every record in each each other table so if each of the seven tables has 10 records, you query 10,000,000 rows!

 

You could try

SELECT 'grooming_centers' as name, AVG(SIR) as sir FROM grooming_centers
UNION
SELECT 'barenas' as name, AVG(SIR) as sir FROM barenas
UNION
SELECT 'caves' as name, AVG(SIR) as sir FROM caves
UNION
SELECT 'tanks' as name, AVG(SIR) as sir FROM tanks
UNION
SELECT 'training_areas' as name, AVG(SIR) as sir FROM training_areas
UNION
SELECT 'vet_clinics' as name, AVG(SIR) as sir FROM vet_clinics
UNION
SELECT 'shops' as name, AVG(SIR) as sir FROM shops

[neel_basu]

THanks I didn't know the AVG() function.

[kernelgpf]

That worked wonders, thanks Barand! =]