ch05.htm

Learn SQL in an easy way...

and are assigned their own unique groupings.</P>
<P>The next example finds the largest and smallest amounts, grouped by <TT>REMARKS</TT>:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT MIN(AMOUNT), MAX(AMOUNT)
FROM CHECKS
GROUP BY REMARKS;</B>

            MIN             MAX
=============== ===============

         245.34          245.34
           10.5            10.5
         200.33          200.33
          15.75              98
            150             150
            150             150
             34              34
             60              60
             34              34
           87.5            87.5
             25              25
          24.35           24.35
</FONT></PRE>
<P>Here's what will happen if you try to include in the <TT>select</TT> statement
a column that has several different values within the group formed by <TT>GROUP BY</TT>:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT PAYEE, MAX(AMOUNT), MIN(AMOUNT)
FROM CHECKS
GROUP BY REMARKS;</B>

Dynamic SQL Error
-SQL error code = -104
-invalid column reference
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This query tries to group <TT>CHECKS</TT> by <TT>REMARK</TT>. When the query finds
two records with the same <TT>REMARK</TT> but different <TT>PAYEE</TT>s, such as
the rows that have <TT>GAS</TT> as a <TT>REMARK</TT> but have <TT>PAYEE</TT>s of
<TT>LOCAL</TT> <TT>UTILITIES</TT> and <TT>JOANS</TT> <TT>GAS</TT>, it throws an error.</P>
<P>The rule is, Don't use the <TT>SELECT</TT> statement on columns that have multiple
values for the <TT>GROUP BY</TT> clause column. The reverse is not true. You can
use <TT>GROUP BY</TT> on columns not mentioned in the <TT>SELECT</TT> statement.
For example:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT PAYEE, COUNT(AMOUNT)
FROM CHECKS
GROUP BY PAYEE, AMOUNT;
</B>
PAYEE                      COUNT
==================== ===========

Abes Cleaners                  1
Abes Cleaners                  1
Cash                           1
Cash                           1
Cash                           1
Joans Gas                      1
Joans Gas                      1
Joes Stale $ Dent              1
Local Utilities                1
Local Utilities                1
Local Utilities                1
Ma Bell                        1
Ma Bell                        1
Reading R.R.                   1
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This silly query shows how many checks you had written for identical amounts to
the same <TT>PAYEE</TT>. Its real purpose is to show that you can use <TT>AMOUNT</TT>
in the <TT>GROUP BY</TT> clause, even though it is not mentioned in the <TT>SELECT</TT>
clause. Try moving <TT>AMOUNT</TT> out of the <TT>GROUP BY</TT> clause and into the
<TT>SELECT</TT> clause, like this:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT PAYEE, AMOUNT, COUNT(AMOUNT)
FROM CHECKS
GROUP BY PAYEE;</B>

Dynamic SQL Error
-SQL error code = -104
-invalid column reference
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>SQL cannot run the query, which makes sense if you play the part of SQL for a
moment. Say you had to group the following lines:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT PAYEE, AMOUNT, REMARKS
FROM CHECKS
WHERE PAYEE ='Cash';</B>

PAYEE                         AMOUNT REMARKS
==================== =============== ===============

Cash                              25 Wild Night Out
Cash                              60 Trip to Boston
Cash                              34 Trip to Dayton
</FONT></PRE>
<P>If the user asked you to output all three columns and group by <TT>PAYEE</TT>
only, where would you put the unique remarks? Remember you have only one row per
group when you use <TT>GROUP BY</TT>. SQL can't do two things at once, so it complains:
<TT>Error #31: Can't do two things at once.</TT>
<H2><FONT COLOR="#000077">The</FONT><FONT SIZE="6" COLOR="#000077"> <TT>HAVING</TT></FONT><FONT
COLOR="#000077"> Clause</FONT></H2>
<P>How can you qualify the data used in your <TT>GROUP BY</TT> clause? Use the table
<TT>ORGCHART</TT> and try this:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT * FROM ORGCHART;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#000000"></FONT></PRE>
<PRE><FONT COLOR="#0066FF">NAME            TEAM          SALARY   SICKLEAVE ANNUALLEAVE
=============== ======== =========== =========== ===========

ADAMS           RESEARCH    34000.00          34          12
WILKES          MARKETING   31000.00          40           9
STOKES          MARKETING   36000.00          20          19
MEZA            COLLECTIONS 40000.00          30          27
MERRICK         RESEARCH    45000.00          20          17
RICHARDSON      MARKETING   42000.00          25          18
FURY            COLLECTIONS 35000.00          22          14
PRECOURT        PR          37500.00          24          24
</FONT></PRE>
<P>If you wanted to group the output into divisions and show the average salary in
each division, you would type:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT TEAM, AVG(SALARY)
FROM ORGCHART
GROUP BY TEAM;
</B>
TEAM                    AVG
=============== ===========

COLLECTIONS        37500.00
MARKETING          36333.33
PR                 37500.00
RESEARCH           39500.00
</FONT></PRE>
<P>The following statement qualifies this query to return only those departments
with average salaries under <TT>38000</TT>:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT TEAM, AVG(SALARY)
FROM ORGCHART
WHERE AVG(SALARY) &lt; 38000
GROUP BY TEAM;</B>

Dynamic SQL Error
-SQL error code = -104

-Invalid aggregate reference
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This error occurred because <TT>WHERE</TT> does not work with aggregate functions.
To make this query work, you need something new: the <TT>HAVING</TT> clause. If you
type the following query, you get what you ask for:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT TEAM, AVG(SALARY)
FROM ORGCHART
GROUP BY TEAM
HAVING AVG(SALARY) &lt; 38000;</B>
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">TEAM                    AVG
=============== ===========

COLLECTIONS        37500.00
MARKETING          36333.33
PR                 37500.00
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P><TT>HAVING</TT> enables you to use aggregate functions in a comparison statement,
providing for aggregate functions what <TT>WHERE</TT> provides for individual rows.
Does <TT>HAVING</TT> work with nonaggregate expressions? Try this:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT TEAM, AVG(SALARY)
FROM ORGCHART
GROUP BY TEAM
HAVING SALARY &lt; 38000;
</B>
TEAM                    AVG
=============== ===========

PR                 37500.00
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Why is this result different from the last query? The <TT>HAVING AVG(SALARY) &lt;
38000</TT> clause evaluated each grouping and returned only those with an average
salary of under <TT>38000</TT>, just what you expected. <TT>HAVING SALARY &lt; 38000</TT>,
on the other hand, had a different outcome. Take on the role of the SQL engine again.
If the user asks you to evaluate and return groups of divisions where <TT>SALARY
&lt; 38000</TT>, you would examine each group and reject those where an individual
<TT>SALARY</TT> is greater than <TT>38000</TT>. In each division except PR, you would
find at least one salary greater than <TT>38000</TT>:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT NAME, TEAM, SALARY
FROM ORGCHART
ORDER BY TEAM;</B>

NAME            TEAM                 SALARY
=============== =============== ===========

FURY            COLLECTIONS        35000.00
MEZA            COLLECTIONS        40000.00
WILKES          MARKETING          31000.00
STOKES          MARKETING          36000.00
RICHARDSON      MARKETING          42000.00
PRECOURT        PR                 37500.00
ADAMS           RESEARCH           34000.00
MERRICK         RESEARCH           45000.00
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Therefore, you would reject all other groups except PR. What you really asked
was <TT>Select all groups where no individual makes more than 38000</TT>. Don't you
just hate it when the computer does exactly what you tell it to?


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>WARNING:</B></FONT><B> </B>Some implementations of SQL return
	an error if you use anything other than an aggregate function in a <TT>HAVING</TT>
	clause. Don't bet the farm on using the previous example until you check the implementation
	of the particular SQL you use. 
<HR>


</BLOCKQUOTE>

<P>Can you use more than one condition in your <TT>HAVING</TT> clause? Try this:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT TEAM, AVG(SICKLEAVE),AVG(ANNUALLEAVE)
FROM ORGCHART
GROUP BY TEAM
HAVING AVG(SICKLEAVE)&gt;25 AND
AVG(ANNUALLEAVE)&lt;20;
</B></FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The following table is grouped by <TT>TEAM</TT>. It shows all the teams with <TT>SICKLEAVE</TT>
averages above <TT>25</TT> days and <TT>ANNUALLEAVE</TT> averages below <TT>20</TT>
days.</P>
<H5>OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">TEAM                    AVG         AVG
=============== =========== ===========

MARKETING                28          15
RESEARCH                 27          15
</FONT></PRE>
<P>You can also use an aggregate function in the <TT>HAVING</TT> clause that was
not in the <TT>SELECT</TT> statement. For example:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT TEAM, AVG(SICKLEAVE),AVG(ANNUALLEAVE)
FROM ORGCHART
GROUP BY TEAM
HAVING COUNT(TEAM) &gt; 1;
</B>
TEAM                    AVG         AVG
=============== =========== ===========

COLLECTIONS              26          21
MARKETING                28          15
RESEARCH                 27          15
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This query returns the number of <TT>TEAM</TT>s with more than one member. <TT>COUNT(TEAM</TT>)
is not used in the <TT>SELECT</TT> statement but still functions as expected in the
<TT>HAVING</TT> clause.</P>
<P>The other logical operators all work well within the <TT>HAVING</TT> clause. Consider
this:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT TEAM,MIN(SALARY),MAX(SALARY)
FROM ORGCHART
GROUP BY TEAM
HAVING AVG(SALARY) &gt; 37000
OR
MIN(SALARY) &gt; 32000;</B>

TEAM                    MIN         MAX
=============== =========== ===========

COLLECTIONS        35000.00    40000.00
PR                 37500.00    37500.00
RESEARCH           34000.00    45000.00
</FONT></PRE>
<P>The operator <TT>IN</TT> also works in a <TT>HAVING</TT> clause, as demonstrated
here:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT TEAM,AVG(SALARY)
FROM ORGCHART
GROUP BY TEAM
HAVING TEAM IN ('PR','RESEARCH');</B>

TEAM                    AVG
=============== ===========

PR                 37500.00
RESEARCH           39500.00
</FONT></PRE>
<H2><FONT COLOR="#000077">Combining Clauses</FONT></H2>
<P>Nothing exists in a vacuum, so this section takes you through some composite examples
that demonstrate how combinations of clauses perform together.
<H4><FONT COLOR="#000077">Example 5.1</FONT></H4>
<P>Find all the checks written for Cash and Gas in the <TT>CHECKS</TT> table and
order them by <TT>REMARKS</TT>.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT PAYEE, REMARKS
FROM CHECKS
WHERE PAYEE = 'Cash'
OR REMARKS LIKE'Ga%'
ORDER BY REMARKS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">PAYEE                REMARKS
==================== ====================

Joans Gas            Gas
Joans Gas            Gas
Local Utilities      Gas
Cash                 Trip to Boston
Cash                 Trip to Dayton
Cash                 Wild Night Out
</FONT></PRE>