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.

Er, does anyone follow me or am I barking mad.

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.

> Er, does anyone follow me or am I barking mad.

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.

>Er, does anyone follow me or am I barking mad.

>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.
Madison Pruet


Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Small and easy question (I think) :)

2. I think it's easy to do

3. Think this is an easy query question

4. Easy (I think) SQL Statement question

5. Sql Server and ODBC Access2000 - fairly easy one I think

6. Easy I think

7. Stored procedure in select (easy question i think)

8. Why does everyone think MySQL is easier?

9. 3 easy problem i think, Help Help

10. Easy ADO Question (I Think)

11. Thought it would be easy but

12. An easy problem, I Think...


 
Powered by phpBB® Forum Software