[thelist] SQL - Invalid use of group function
rudy
r937 at interlog.com
Thu Aug 16 08:01:18 CDT 2001
>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/
More information about the thelist
mailing list