Re: GROUP BY clause

On Fri, 07 Sep 2007 13:14:45 -0700, brenda.beltran_at_gmail.com wrote:

>Hi everybody,
>
>I just got to this area, so hope someone can help me with the
>following issue, I will really appreciate it.
>
>I want to query a column that is not part of the GROUP BY clause:
>
>SELECT c.country_id,
> r.region_id,
> r.manag_id,
> SUM(r.sls),
> SUM(r.cst)
>FROM country c INNER JOIN region r ON c.country_key = r.country_key
>GROUP BY c.country_id,
> r.region_id;
>
>Obviously, it's complaining because r.manag_id is not in the GROUP
>BY clause, but I need the result set to be as defined in the column
>clause. I wonder if there is any way to have these same columns in my
>query keeping my GROUP BY clause as it is now.
>
>Thanks very much for your suggestions!

Well, yes, just supply some text or a subquery for that COLUMN.

Technically, a COLUMN can never be returned in a GROUP BY query, because a GROUP BY aggregates, and a COLUMN on its own is not an aggregate value.

However, if it appears in the GROUP BY caluse, the aggregation is done for each set of records where the value for that COLUMN is exactly the same. As such, it is as if it was agregated. To wit, add MIN(), MAX(), or the like to it, and the value will not change, simply because in every record it is always the same value.

If the COLUMN does not appear in the GROUP BY clause, it *technicallly* can have different value within one of the individual groups. If so, which one should the database return?

So, to return a COLUMN in a GROUP BY query, it must be guaranteed to always have the same value for that group. This can be done by:

1. Listing in the GROUP BY clause.
2. Using an aggregate FUNCTION.
3. Supplying a literal value.
4. Using a sub-query that returns a single record.

B. Received on Mon Sep 10 2007 - 08:40:50 CDT