comparisons

[Jason777]

Hello all, Everyone was very helpful in my last post, I have another question for you.

I have 2 tables
commr and ctac1

commr looks like
invoice customer extended price
------- -------- --------------
100 Bill 100.00
101 Ted 50.00
102 Ralph 40.00


ctac1 looks like

invoice amt_paid
------- --------
100 10.00
100 20.00
101 50.00
102 20.00
100 70.00
102 10.00


I need to run a query to pull records off commr. only when the sum of amt_paid is equal to the extended price for the same invoice #. Any suggestions? everything I try either pulls a million records, 0 records or 1 record...

 

[dan1967]

Try this:

SELECT commr.invoice, commr.customer, commr.extended_price, sum(ctac1.amt_paid) as 'AmtPaid'
FROM commr
JOIN ctac1 on commr.invoice = ctac1.invoice
WHERE [criteria]
GROUP BY commr.invoice, commr.customer, commr.extended_price
HAVING sum(ctac1.amt_paid) = commr.extended_price

The HAVING clause sets a criteria to the results that an aggregate function must meet. These types of criteria don't work in WHERE as that clause doesn't handle aggregates.

Dan.

 

[ClaireHCM]

select * from com a inner join
(select invoice,sum(amt_paid) as price from ctac1 group by invoice) B
on a.price = b.price and a.invoice = b.invoice



-----This is my com(mr) and ctac1 table

create table com(invoice int,cust varchar(20),price decimal(6,2))

insert into com select 100, 'Bill', 100.00
insert into com select 101, 'Ted' , 50.00
insert into com select 102, 'Ralph', 40.00

ctac1 looks like

create table ctac1(invoice int,amt_paid decimal(6,2))

insert into ctac1 select 100 , 10.00
insert into ctac1 select 100, 20.00
insert into ctac1 select 101, 50.00
insert into ctac1 select 102, 20.00
insert into ctac1 select 100, 70.00
insert into ctac1 select 102 , 10.00

 

[Jason777]

Thanks for the quick responses, I will try them when I get back in the office today.