RE: SQL question

[Buck Calabro <mcalabro@xxxxxxxxxxxx>]

Mattias,
Use UNION.  In essence, you will do 2 SELECT statements.  Each SELECT will
have the same number and type of columns.  The first will produce the detail
and the second will produce the total.  UNION combines them into one final
result table.

SELECT desc,amount, ' ' from itemfile UNION ALL
SELECT 'TOTAL',sum(amount),'*' from itemfile
order by 3,1

The third column is to sort the total line after all the detail lines.

I often use this technique when JOINing; the first SELECT has all the
records that match, and the second has all the records that don't.  I can
UNION the two tables to produce a list of all records in the primary file,
with "NO MATCH" defaults on the records that have bad data:

SELECT desc,amount,itemvend,vendname from itemfile,vendfile where itemvend =
vendvend
UNION ALL
   SELECT desc,amount,itemvend,'INVALID VENDOR' from itemfile where itemvend
not in
      (SELECT vendvend from vendfile)
order by 4,1

There's probably a better way, but I am ashamed to say that this is how I
learnt many years ago, and I haven't visited the latest SQL manuals yet.

Buck Calabro
Aptis; Albany, NY

> -----Original Message-----
> From: Matthias Oertli 
> Sent: Wednesday, November 03, 1999 5:28 AM
> To:   midrange-l@midrange.com
> 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
> 
+---
| 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
+---