Re: Simple(?) SQL problem

Francis Fang wrote: > > Hi folks. Hoping I can get some help on a problem that seems to be > stumping me. Probably due my lack of experience with SQL. > > Here's the db: > > Table Job Table Transaction > job_id <=======> txn_job_id > job_desc txn_lbr_val > job_invoice txn_aid > job_date > > What I need is an SQL statement that would retrieve: > > job_id, job_desc, job_invoice, sum(txn_lbr_val), sum(txn_lbr_val) > ^ ^ > where txn_aid=1000 | > where txn_aid=2000 > > All of this where job_date > "12/1/96" > > The stumbling block is how I would get the sum of the multiple > transaction records for each job_id for each txn_aid. > > Any and all pointers would be most appreciated. > > TIA. > Frank. What you need is a group by clause in the select. Give this a try: select job_id, job_desc, job_invoice, txn_aid, sum(txn_lbr_val) from job, transaction where job.job_id = txn_job_id and txn_aid in (1000,2000) group by 1,2,3,4 Note that this will return multiple rows for the txn_aid also. I hope this helps... Matt W.