• Subject: RE: SQL question
  • From: Buck Calabro <mcalabro@xxxxxxxxxxxx>
  • Date: Wed, 3 Nov 1999 09:24:14 -0500

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
+---

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2020 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].