Hi.
I'm trying to get an average value (avg()) of a certain attribute in a
table. The snag is, I don't want this across a whole table. I only want it
over a certain set of records. In effect, I want to do something like:
SELECT somefield
FROM sometable
WHERE otherfield = 'criteria'
ORDER BY thirdfield
LIMIT 10
and then do an avg(somefield).
Can this be done without using temp tables, in a single query? I tried
making a function that does this, but even if I used temp tables, the
function didn't work, claiming that the table didn't exist.
Ideally, I want to do something like:
SELECT avg
(
SELECT somefield
FROM sometable
WHERE otherfield = 'criteria'
ORDER BY thirdfield
LIMIT 10
) as somefieldname
But the parser doesn't seem to like it.
How can I do this?
Thanks.
Gordan
