GROUP BY...easier way to think about it
Author Message
GROUP BY...easier way to think about it

Hi All,

For goodness sake people, I'm still trying to get my aching brain
around this thing of GROUP BY :-)

One way I have tried to think about it is to view a GROUP BY statement
on 1 or more rows as though it were selecting a distinct row. That is:

SELECT field1, field2, field3
FROM table
WHERE conditions
GROUP BY 1, 2, 3

Where, in effect, the rows returned will be unique and that the
combination of the 3 fields is acting as a key.

Someone pass me a gun :-)

Cheers, Ian.
Tedious 'office speak' part 3: "Let's go scuba in the think tank."

Wed, 18 Jun 1902 08:00:00 GMT
GROUP BY...easier way to think about it

Quote:

> Hi All,

> For goodness sake people, I'm still trying to get my aching brain
> around this thing of GROUP BY :-)

> One way I have tried to think about it is to view a GROUP BY statement
> on 1 or more rows as though it were selecting a distinct row. That is:

> SELECT field1, field2, field3
> FROM table
> WHERE conditions
> GROUP BY 1, 2, 3

> Where, in effect, the rows returned will be unique and that the
> combination of the 3 fields is acting as a key.

Wellll...  In your specific example the GROUP BY has indeed trivially
become a DISTINCT but probably a little more expensive.  Generally
GROUP BY is used to sumarize records on some set of columns and return
and AGGREGATE value (like COUNT(), SUM(), AVG(), MAX(), MIN()) for
each unique set of the key columns.  Ex:

SELECT s.DeptNo, DeptName, SUM(Salary)
FROM salary s dept d
WHERE s.deptno = d.deptno
GROUP BY 1, 2;

This returns the total salary budget by department.

Art S. Kagel

Wed, 18 Jun 1902 08:00:00 GMT
GROUP BY...easier way to think about it

Quote:
>Hi All,

>For goodness sake people, I'm still trying to get my aching brain
>around this thing of GROUP BY :-)

>One way I have tried to think about it is to view a GROUP BY statement
>on 1 or more rows as though it were selecting a distinct row. That is:

>SELECT field1, field2, field3
>FROM table
>WHERE conditions
>GROUP BY 1, 2, 3

>Where, in effect, the rows returned will be unique and that the
>combination of the 3 fields is acting as a key.

>Someone pass me a gun :-)

>Cheers, Ian.

The mail reason to use a group by is for aggregrate function such as average(),
sum(), max(), etc.  Basically this provides the ability to create "subtotals"
by key value.  You would group by the "key" columns and sum(), average(),
count(), etc. the other columns.