Re: SQL question

and the answer is...

Yup, but...

To do this on the 400, you need two separate select statements linked by a
union:

select field1, sum(amount)
  from testroll
  group by field1
union
select 'Total', sum(amount) from testroll

Note that unless you specify the exact same number of columns and their
types match on both selects, the union will not work.  Also note that the
default will sort the Total in with the field1 values so that it may not
appear at the bottom of the list. You could fix this by doing '99 Total:' or
something that will force the field1 column to put the total at the end.

===========================================================
R. Bruce Hoffman, Jr.
 -- IBM Certified AS/400 Professional System Administrator
 -- IBM Certified AS/400 Professional Network Administrator

"The sum of all human knowledge is a fixed constant.
    It's the population that keeps growing!"

-----Original Message-----
From: Matthias Oertli <oertlim@s054.aone.net.au>
To: midrange-l@midrange.com <midrange-l@midrange.com>
Date: Wednesday, November 03, 1999 7:26 AM
Subject: SQL question


>This is probably off topic but I have to ask anyway:
>Using sql is it possible to display a few records AND total them up at
>the bottom in one hit, e.g.:
>
>ITEM1 $10
>ITEM2   $50
>TOTAL   $60
>
>If yes, how?
>Regards,
>Matthias
>
>---------------------------------------------------------------------
>Matthias Oertli   <oertlim@s054.aone.net.au>
>+---
>| This is the Midrange System Mailing List!
>| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
>| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
>| To unsubscribe from this list send email to
MIDRANGE-L-UNSUB@midrange.com.
>| Questions should be directed to the list owner/operator:
david@midrange.com
>+---
>
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---