Printing average-value from two tables with PHP/SQL

[ijjed]

Hi,

 

Firstly, these are my tables:

 

CLASS

class_id                class_name

1                          Donkeys

2                          Monkeys

4                          Classic

9                          Humans

 

COURSES

class_id                courses_number

9                          6

9                          2

1                          3

2                          2

 

 

I would like to print average course_number for each class_name.

So for class_name Donkeys would have average course_number 3.

And for clasS_name Humans, average course_number is 4.

 

Do you get my point?

 

 

 

[Psycho]

SELECT class_name, AVG(course_number) as average

FROM class

JOIN courses USING (class_id)

GROUP BY class_id

[ijjed]

Unbeliavable! It worked like a charm! Thanks! 

 

 

How about if class_name doesn't have any value? Then it would just print empty or "0" value?

 

Now it works, but it wont print other class_names. In that example the class_name "Classic" would not be listed. I would like to have it listed too.

[Psycho]

Then you need to do a LEFT JOIN so you will get ALL records from the first (i.e. Left) table.

SELECT class_name, AVG(course_number) as average
FROM class
LEFT JOIN courses USING (class_id)
GROUP BY class_id

But, the average results for those without any corresponding records in the "courses" will be an empty value. You can use PHP to convert that to a 0.

 

Or you can have the query do that for you. Not sure on the exact syntax and I don't have time to test

SELECT class_name, AVG(IF(course_number<>NULL, course_number, 0)) as average
FROM class
LEFT JOIN courses USING (class_id)
GROUP BY class_id