Re: Simple(?) SQL problem
Francis Fang wrote:
> 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.
I think this will work, I'm using a trick I saw posted not too
long ago, someone else said depending on your version it may not
work:
select job_id, job_desc, job_invoice,
(select sum(txn_lbr_val)
from transaction
where job_id=txn_job_id
and txn_aid=1000),
(select sum(txn_lbr_val)
where job_id=txn_job_id
and txn_aid=2000)
from job
where job_date > "12/1/1996"
Good Luck
Doug
*** Disclaimer: These are the opinions of the poster not Amgen Inc.***