ch05.htm

Learn SQL in an easy way...

letter first? You're in luck! The following query generates a list of <TT>PAYEE</TT>s
that stars at the end of the alphabet:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT *
</B>  2<B>  FROM CHECKS
</B>  3<B>  ORDER BY PAYEE DESC;</B>

  CHECK# PAYEE                  AMOUNT REMARKS
-------- -------------------- -------- ------------------
       2 Reading R.R.           245.34 Train to Chicago
       1 Ma Bell                   150 Have sons next time
       3 Ma Bell                200.32 Cellular Phone
       4 Local Utilities            98 Gas
       5 Joes Stale $ Dent         150 Groceries
      17 Joans Gas                25.1 Gas
      16 Cash                       25 Wild Night Out
       8 Cash                       60 Trip to Boston
      21 Cash                       34 Trip to Dayton
       9 Abes Cleaners           24.35 X-Tra Starch
      20 Abes Cleaners            10.5 All Dry Clean

11 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The <TT>DESC</TT> at the end of the <TT>ORDER BY</TT> clause orders the list in
descending order instead of the default (ascending) order. The rarely used, optional
keyword <TT>ASC</TT> appears in the following statement:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT PAYEE, AMOUNT
  </B>2<B>  FROM CHECKS
 </B> 3 <B> ORDER BY CHECK# ASC;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">PAYEE                   AMOUNT
-------------------- ---------
Ma Bell                    150
Reading R.R.            245.34
Ma Bell                 200.32
Local Utilities             98
Joes Stale $ Dent          150
Cash                        60
Abes Cleaners            24.35
Cash                        25
Joans Gas                 25.1
Abes Cleaners             10.5
Cash                        34

11 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The ordering in this list is identical to the ordering of the list at the beginning
of the section (without <TT>ASC</TT>) because <TT>ASC</TT> is the default. This query
also shows that the expression used after the <TT>ORDER BY</TT> clause does not have
to be in the <TT>SELECT</TT> statement. Although you selected only <TT>PAYEE</TT>
and <TT>AMOUNT</TT>, you were still able to order the list by <TT>CHECK#</TT>.</P>
<P>You can also use <TT>ORDER BY</TT> on more than one field. To order <TT>CHECKS</TT>
by <TT>PAYEE</TT> and <TT>REMARKS</TT>, you would query as follows:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT *
  </B>2<B>  FROM CHECKS
  </B>3<B>  ORDER BY PAYEE, REMARKS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">  CHECK# PAYEE                  AMOUNT REMARKS
-------- -------------------- -------- ------------------
      20 Abes Cleaners            10.5 All Dry Clean
       9 Abes Cleaners           24.35 X-Tra Starch
       8 Cash                       60 Trip to Boston
      21 Cash                       34 Trip to Dayton
      16 Cash                       25 Wild Night Out
      17 Joans Gas                25.1 Gas
       5 Joes Stale $ Dent         150 Groceries
       4 Local Utilities            98 Gas
       3 Ma Bell                200.32 Cellular Phone
       1 Ma Bell                   150 Have sons next time
       2 Reading R.R.           245.34 Train to Chicago
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Notice the entries for Cash in the <TT>PAYEE</TT> column. In the previous <TT>ORDER
BY</TT>, the <TT>CHECK#</TT>s<TT> </TT>were in the order 16, 21, 8. Adding the field
<TT>REMARKS</TT> to the <TT>ORDER BY</TT> clause puts the entries in alphabetical
order according to <TT>REMARKS</TT>. Does the order of multiple columns in the <TT>ORDER
BY</TT> clause make a difference? Try the same query again but reverse <TT>PAYEE</TT>
and <TT>REMARKS</TT>:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT *
  </B>2<B>  FROM CHECKS
 </B> 3<B>  ORDER BY REMARKS, PAYEE;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT><FONT COLOR="#0066FF"></FONT></H5>
<PRE><FONT COLOR="#0066FF">  CHECK# PAYEE                  AMOUNT REMARKS
-------- -------------------- -------- --------------------
      20 Abes Cleaners            10.5 All Dry Clean
       3 Ma Bell                200.32 Cellular Phone
      17 Joans Gas                25.1 Gas
       4 Local Utilities            98 Gas
       5 Joes Stale $ Dent         150 Groceries
       1 Ma Bell                   150 Have sons next time
       2 Reading R.R.           245.34 Train to Chicago
       8 Cash                       60 Trip to Boston
      21 Cash                       34 Trip to Dayton
      16 Cash                       25 Wild Night Out
       9 Abes Cleaners           24.35 X-Tra Starch

11 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>As you probably guessed, the results are completely different. Here's how to list
one column in alphabetical order and list the second column in reverse alphabetical
order:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT *
  </B>2<B>  FROM CHECKS
  </B>3 <B> ORDER BY PAYEE ASC, REMARKS DESC;</B>

  CHECK# PAYEE                  AMOUNT REMARKS
-------- -------------------- -------- ------------------
       9 Abes Cleaners           24.35 X-Tra Starch
      20 Abes Cleaners            10.5 All Dry Clean
      16 Cash                       25 Wild Night Out
      21 Cash                       34 Trip to Dayton
       8 Cash                       60 Trip to Boston
      17 Joans Gas                25.1 Gas
       5 Joes Stale $ Dent         150 Groceries
       4 Local Utilities            98 Gas
       1 Ma Bell                   150 Have sons next time
       3 Ma Bell                200.32 Cellular Phone
       2 Reading R.R.           245.34 Train to Chicago

11 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>In this example <TT>PAYEE</TT> is sorted alphabetically, and <TT>REMARKS</TT>
appears in descending order. Note how the remarks in the three checks with a <TT>PAYEE</TT>
of Cash are sorted.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>If you know that a column you want
	to order your results by is the first column in a table, then you can type <TT>ORDER
	BY 1</TT> in place of spelling out the column name. See the following example. 
<HR>
</P>

</BLOCKQUOTE>

<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT *
 </B> 2<B>  FROM CHECKS
  </B>3<B>  ORDER BY 1;</B>

  CHECK# PAYEE                  AMOUNT REMARKS
-------- -------------------- -------- ------------------
       1 Ma Bell                   150 Have sons next time
       2 Reading R.R.           245.34 Train to Chicago
       3 Ma Bell                200.32 Cellular Phone
       4 Local Utilities            98 Gas
       5 Joes Stale $ Dent         150 Groceries
       8 Cash                       60 Trip to Boston
       9 Abes Cleaners           24.35 X-Tra Starch
      16 Cash                       25 Wild Night Out
      17 Joans Gas                25.1 Gas
      20 Abes Cleaners            10.5 All Dry Clean
      21 Cash                       34 Trip to Dayton

11 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This result is identical to the result produced by the <TT>SELECT</TT> statement
that you used earlier today:</P>
<PRE><FONT COLOR="#0066FF">SELECT * FROM CHECKS ORDER BY CHECK#;
</FONT></PRE>
<H2><FONT COLOR="#000077">The GROUP BY Clause</FONT></H2>
<P>On Day 3 you learned how to use aggregate functions (<TT>COUNT</TT>, <TT>SUM</TT>,
<TT>AVG</TT>, <TT>MIN</TT>, and <TT>MAX</TT>). If you wanted to find the total amount
of money spent from the slightly changed <TT>CHECKS</TT> table, you would type:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT *
FROM CHECKS;
</B></FONT></PRE>
<P>Here's the modified table:</P>
<H5>OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">CHECKNUM PAYEE                AMOUNT  REMARKS
======== =========== ===============  ======================

       1 Ma Bell                 150  Have sons next time
       2 Reading R.R.         245.34  Train to Chicago
       3 Ma Bell              200.33  Cellular Phone
       4 Local Utilities          98  Gas
       5 Joes Stale $ Dent       150  Groceries
      16 Cash                     25  Wild Night Out
      17 Joans Gas              25.1  Gas
       9 Abes Cleaners         24.35  X-Tra Starch
      20 Abes Cleaners          10.5  All Dry Clean
       8 Cash                     60  Trip to Boston
      21 Cash                     34  Trip to Dayton
      30 Local Utilities        87.5  Water
      31 Local Utilities          34  Sewer
      25 Joans Gas             15.75  Gas
</FONT></PRE>
<P>Then you would type:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT SUM(AMOUNT)
FROM CHECKS;</B>

            SUM
===============

        1159.87
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This statement returns the sum of the column <TT>AMOUNT</TT>. What if you wanted
to find out how much you have spent on each <TT>PAYEE</TT>? SQL helps you with the
<TT>GROUP BY</TT> clause. To find out whom you have paid and how much, you would
query like this:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT PAYEE, SUM(AMOUNT)
FROM CHECKS
GROUP BY PAYEE;</B>

PAYEE                            SUM
==================== ===============

Abes Cleaners              34.849998
Cash                             119
Joans Gas                  40.849998
Joes Stale $ Dent                150
Local Utilities                219.5
Ma Bell                    350.33002
Reading R.R.                  245.34
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The <TT>SELECT</TT> clause has a normal column selection, <TT>PAYEE</TT>, followed
by the aggregate function <TT>SUM(AMOUNT</TT>). If you had tried this query with
only the <TT>FROM CHECKS</TT> that follows, here's what you would see:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT PAYEE, SUM(AMOUNT)
FROM CHECKS;
</B>
Dynamic SQL Error
-SQL error code = -104
-invalid column reference
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>SQL is complaining about the combination of the normal column and the aggregate
function. This condition requires the <TT>GROUP BY</TT> clause. <TT>GROUP BY</TT>
runs the aggregate function described in the <TT>SELECT</TT> statement for each grouping
of the column that follows the <TT>GROUP BY</TT> clause. The table <TT>CHECKS</TT>
returned 14 rows when queried with <TT>SELECT * FROM CHECKS</TT>. The query on the
same table, <TT>SELECT PAYEE, SUM(AMOUNT) FROM CHECKS GROUP BY PAYEE</TT>, took the
14 rows in the table and made seven groupings, returning the <TT>SUM</TT> of each
grouping.</P>
<P>Suppose you wanted to know how much you gave to whom with how many checks. Can
you use more than one aggregate function?</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT PAYEE, SUM(AMOUNT), COUNT(PAYEE)
FROM CHECKS
GROUP BY PAYEE;</B>

PAYEE                            SUM       COUNT
==================== =============== ===========

Abes Cleaners              34.849998           2
Cash                             119           3
Joans Gas                  40.849998           2
Joes Stale $ Dent                150           1
Local Utilities                219.5           3
Ma Bell                    350.33002           2
Reading R.R.                  245.34           1
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This SQL is becoming increasingly useful! In the preceding example, you were able
to perform group functions on unique groups using the <TT>GROUP BY </TT>clause. Also
notice that the results were ordered by payee. <TT>GROUP BY</TT> also acts like the
<TT>ORDER BY</TT> clause. What would happen if you tried to group by more than one
column? Try this:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT PAYEE, SUM(AMOUNT), COUNT(PAYEE)
FROM CHECKS
GROUP BY PAYEE, REMARKS;
</B>
PAYEE                            SUM       COUNT
==================== =============== ===========

Abes Cleaners                   10.5           1
Abes Cleaners                  24.35           1
Cash                              60           1
Cash                              34           1
Cash                              25           1
Joans Gas                  40.849998           2
Joes Stale $ Dent                150           1
Local Utilities                   98           1
Local Utilities                   34           1
Local Utilities                 87.5           1
Ma Bell                       200.33           1
Ma Bell                          150           1
Reading R.R.                  245.34           1
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The output has gone from 7 groupings of 14 rows to 13 groupings. What is different
about the one grouping with more than one check associated with it? Look at the entries
for Joans Gas:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT PAYEE, REMARKS
FROM CHECKS
WHERE PAYEE = 'Joans Gas';</B>

PAYEE                REMARKS
==================== ====================

Joans Gas            Gas
Joans Gas            Gas
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>You see that the combination of <TT>PAYEE</TT> and <TT>REMARKS</TT> creates identical
entities, which SQL groups together into one line with the <TT>GROUP BY</TT> clause.
The other rows produce unique combinations of <TT>PAYEE</TT> and <TT>REMARKS</TT>