Path: utzoo!utgpu!news-server.csri.toronto.edu!rpi!zaphod.mps.ohio-state.edu!uwm.edu!miller.cs.uwm.edu!felicia
From: felicia@miller.cs.uwm.edu
Newsgroups: comp.databases
Subject: Help::: SQL group by/having... Questions!!!
Message-ID: <12146@uwm.edu>
Date: 15 May 91 06:14:50 GMT
Sender: news@uwm.edu
Distribution: na
Organization: University of Wisconsin at Milwaukee
Lines: 86
Originator: felicia@miller.cs.uwm.edu


I thought I understood SQL's group by -having clauses, but they don't
work like I think....

I hope someone would explain to me why the following SQL queries have
such results: )


Assuming table created is:
==========================
Name: pmtbl   
-----------------------------------------
pn      char(8)
pp      char(8)
txt     char(25)
..
..
-----------------------------------------

And, some rows existed in table are:
-----------------------------------------
'    004', NULL, 'Row p004'
'    001', NULL, 'Row p001'
'    005', NULL, 'Row p005'
'    001', NULL, 'Row p001'
'    001', NULL, 'Row p001-3'

-------------------------------------------


Qry 1: 
      select pn from pmtbl group by pn;
::->
-------------------------------------------
    001
    004
    005
 
(total of 3 rows)

Qry 2: 
      select pn, txt from pmtbl group by pn;
::->
------------------------------------------------
    001, Row p001
    001, Row p001
    001, Row p001-3
    004, Row p004
    005, Row p005

(total of 5 rows)

Qry 3: 
      select pn, txt from pmtbl group by pn, txt;
::->
------------------------------------------------
    001, Row p001
    001, Row p001
    001, Row p001-3
    004, Row p004
    005, Row p005

(total of 5 rows)


QUESTIONS:
==========

q1) Why do Qry1 and Qry2 have different results?

q2) If in Qry 3, selected only pn column, should the result be the same?

q3) Does the clause,'group by Col1, Col2' cause the query to retrieve only
    the unique row of (Col1 + Col2) ?

q4) Comparing these two queries:
     select <col1>, <col2> from <tbl1> where ( <conditions C1> )
            group by <col3>

    and
 
     select <col1>, <col2> from <tbl1> 
            group by <col3>
            having ( <conditions C1> )

    Should one of these queries always retrieve faster than the