[thelist] SQL - Invalid use of group function

[rudy]

>MySQL said: Invalid use of group function

hi lauri

i'll get to your problem in a second, but first i want to make a comment

in your SELECT list you have columns and aggregate functions on columns

standard usage of the GROUP BY clause requires you to list all the columns
that are in the select list that aren't in aggregate functions

wrong example --

     select a, b, avg(c)  from tables group by a

right example --

     select a, b, avg(c)  from tables group by a, b

having said this, i also know mysql lets you bend this rule

i am not really experienced with mysql but i would strongly advise sticking
to standard usage

what GROUP BY does is take all the rows produced by the join, sort them,
divide them into groups, and produce *one row per group* (that's the key
point)containing the aggregate(s) for that group

it does not really make sense for the columns in the SELECT list and the
columns in the GROUP BY to be different (despite the explanation in the
mysql docs)

okay, having got that comment off my chest, i notice that in your example,
your list of columns in the GROUP BY clause is the columns you are doing
aggregates on -- switch this around so that your GROUP BY lists the columns
that do *not* have aggregate functions on them

also, if you want to further qualify the results by selecting only those
groups with certain values for their aggregates, use the HAVING clause

AVG(personalia_fees.hinne) >= '1' cannot be right, because AVG()
produces a number

if you want to consider the averages of groups of people where each person
starts with a fee of at least 1, that's different than selecting the groups
of all people where the average is greater than 1

for example, in north america, students are given marks that range from 100
(perfect) down to 0 (rare) and these marks are often categorized as A, B,
etc.

the average mark of only the A students in each school is

    select school, avg(mark)  from students
          where mark => 80
         group by school

whereas the schools with an average mark of A for all students is

    select school, avg(mark)  from students
         group by school
          having avg(mark) => 80

in the first example above, you get an average (and it's going to be over
80, right?) for every school that has at least one A student

in the second, you don't necessarily get all the schools, just those where
the average of *all* the students, including those that didn't get As, is
over 80

finally, i see you using && in the WHERE clause -- i'm only mildly
surprised that this works, but i would stick to the more traditional AND


let me know if this is helpful


rudy
http://rudy.ca/