Page 1 of 1

Trying to select more than 1 column using aggregate function

Posted: Thu Apr 26, 2007 1:46 pm
by impulse()
I'm trying to run the following query, which runs fine, but I also want to select another file. When I try to it says there's an error because it's not part of an aggregate function. Is there a way to select that other column I want?

Code: Select all

SELECT description, SUM(val) as val, ops.product
FROM scheme.opdetm as opd, scheme.opservm ops
WHERE ops.product = opd.product
GROUP BY ops.product
ORDER BY val DESC
Regards,

Posted: Thu Apr 26, 2007 3:02 pm
by timvw
Under the assumption that description is the same for all pairs of ops.product you can:

GROUP BY ops.product, description

Re: Trying to select more than 1 column using aggregate func

Posted: Fri Apr 27, 2007 5:43 pm
by califdon
impulse() wrote:I'm trying to run the following query, which runs fine, but I also want to select another file. When I try to it says there's an error because it's not part of an aggregate function. Is there a way to select that other column I want?

Code: Select all

SELECT description, SUM(val) as val, ops.product
FROM scheme.opdetm as opd, scheme.opservm ops
WHERE ops.product = opd.product
GROUP BY ops.product
ORDER BY val DESC
Not quite sure I understand what you want to do. Do you want to join three tables? That's just a matter of expanding your FROM clause. If that's so, is there a 'val' column in all 3 tables? And do you want the sum total from the 'val' column in all 3?