SQL Query

[Paul Owen]

I have 4 tables

People, address, Payments and invoices the latter 3 link back to people.

I need to pull out the total of all payments and invoices for each person
but only if the sum of paymenst is less than the sum of invoices. and
everything from addresses.

I can do most of it ie get all the bits I need but not just for those that
owe money it returns all rows.

[csomberg]

Post the sql you have already ...

[Ross Presser]

On Tue, 7 Dec 2004 16:08:34 -0000, Paul Owen wrote:

I have 4 tables

People, address, Payments and invoices the latter 3 link back to people.

I need to pull out the total of all payments and invoices for each person
but only if the sum of paymenst is less than the sum of invoices. and
everything from addresses.

I can do most of it ie get all the bits I need but not just for those that
owe money it returns all rows.

Something like this, I'll wager:

select people.Name, address.city,
sum(payments.amount) [PayTotal],
sum(invoices.amount) [InvTotal],
sum(invoices.amount) - sum(payments.amount) [StillOwed]
from people
inner join address on people.ID=address.ID
left join payments on people.ID=payments.ID
left join invoices on people.ID=invoices.ID
having sum(payments.amount) < sum(invoices.amount)

[Paul Owen]

current sql is

select people.Name, address.city,
sum(payments.amount) [PayTotal],
sum(invoices.amount) [InvTotal],
sum(invoices.amount) - sum(payments.amount) [StillOwed]
from people
left join payments on people.ID=payments.ID
left join invoices on people.ID=invoices.ID
group by people.id

returns the right info but for all people, cant quite get the last bit
Still new to this, but getting their slowly.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Hugo Kornelis]

On Tue, 07 Dec 2004 19:53:58 GMT, Paul Owen wrote:

current sql is (snip)returns the right info but for all people

Hi Paul,

Are you sure? You should get errors because people.Name and address.city
are in the select (not in an aggregate) but not in the group by. And
because the address table is not in the from clause at all.

Assuming these errors were made when you copied the query, you should
still get the wrong results. Have you tested it with a combination of a
person with two payments and two invoices? I think you'll the values will
be double what you expect. And it gets much worse if you have five
payments and ten invoices....

Try this one:

SELECT ID, PayTotal, InvTotal,
InvTotal - PayTotal AS StillOwed
FROM (SELECT people.ID,
COALESCE((SELECT SUM(amount)
FROM payments
WHERE payments.ID = people.ID),0) AS PayTotal,
COALESCE((SELECT SUM(amount)
FROM invoices
WHERE invoices.ID = people.ID),0) AS InvTotal
FROM people
GROUP BY people.ID) AS x
WHERE InvTotal > PayTotal

or this one:

SELECT people.ID,
COALESCE (paytot.Total,0) AS PayTotal,
COALESCE (invtot.Total,0) AS InvTotal,
COALECSE (invtot.Total,0)
- COALESCE (paytot.Total,0) AS StillOwed
FROM people
LEFT JOIN (SELECT ID, SUM(amount) AS Total
FROM payments
GROUP BY ID) AS paytot
ON paytot.ID = people.ID
LEFT JOIN (SELECT ID, SUM(amount) AS Total
FROM invoices
GROUP BY ID) AS invtot
ON invtot.ID = people.ID
WHERE COALECSE (invtot.Total,0) > COALESCE (paytot.Total,0)

(both queries untested!)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Paul Owen]

I had this

select people.pid, sum(payments.amount), sum(invoice.totalexvat),
sum(payments.amount) - sum(invoice.totalexvat) as obal
from people
left join payments on people.pid = payments.pid
left join invoice on people.pid = invoice.pid
group by people.pid

and have now added having obal > 1 to the end and it seems to work but have
not cross checked any individuals yet.

Paul

"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:t5********************************@4ax.com...

On Tue, 07 Dec 2004 19:53:58 GMT, Paul Owen wrote:

current sql is

(snip)

returns the right info but for all people

Hi Paul,

Are you sure? You should get errors because people.Name and address.city
are in the select (not in an aggregate) but not in the group by. And
because the address table is not in the from clause at all.

Assuming these errors were made when you copied the query, you should
still get the wrong results. Have you tested it with a combination of a
person with two payments and two invoices? I think you'll the values will
be double what you expect. And it gets much worse if you have five
payments and ten invoices....

Try this one:

SELECT ID, PayTotal, InvTotal,
InvTotal - PayTotal AS StillOwed
FROM (SELECT people.ID,
COALESCE((SELECT SUM(amount)
FROM payments
WHERE payments.ID = people.ID),0) AS PayTotal,
COALESCE((SELECT SUM(amount)
FROM invoices
WHERE invoices.ID = people.ID),0) AS InvTotal
FROM people
GROUP BY people.ID) AS x
WHERE InvTotal > PayTotal

or this one:

SELECT people.ID,
COALESCE (paytot.Total,0) AS PayTotal,
COALESCE (invtot.Total,0) AS InvTotal,
COALECSE (invtot.Total,0)
- COALESCE (paytot.Total,0) AS StillOwed
FROM people
LEFT JOIN (SELECT ID, SUM(amount) AS Total
FROM payments
GROUP BY ID) AS paytot
ON paytot.ID = people.ID
LEFT JOIN (SELECT ID, SUM(amount) AS Total
FROM invoices
GROUP BY ID) AS invtot
ON invtot.ID = people.ID
WHERE COALECSE (invtot.Total,0) > COALESCE (paytot.Total,0)

(both queries untested!)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Hugo Kornelis]

On Wed, 8 Dec 2004 12:19:36 -0000, Paul Owen wrote:

I had this

select people.pid, sum(payments.amount), sum(invoice.totalexvat),
sum(payments.amount) - sum(invoice.totalexvat) as obal
from people
left join payments on people.pid = payments.pid
left join invoice on people.pid = invoice.pid
group by people.pid

and have now added having obal > 1 to the end and it seems to work but have
not cross checked any individuals yet.

Hi Paul,

Test it with this data:

PEOPLE PAYMENTS INVOICES
====== =========== ===============
pid pid amount pid totalexvat
1 1 25 1 100
1 75
2 2 100 2 25
2 75
3 3 40 3 40
3 60 3 60
4 4 20 4 75
4 20 4 25
4 20
4 20
4 20

After testing, get back to my previous post to find some queries that will
work.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Paul Owen]

Hi

I can now see why this does not work, but I have tried your examples and
cant get either of these to work.

I tried starting with a basic query using coalesce but that gives an
error too

SELECT people.pID, COALESCE (payments.amount,0)
FROM people,payments
left join payments on people.pid = payments.pid
group by people.pid
Paul

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Hugo Kornelis]

On 15 Dec 2004 08:35:32 -0600, Paul Owen wrote:

I tried starting with a basic query using coalesce but that gives an
error too

SELECT people.pID, COALESCE (payments.amount,0)
FROM people,payments
left join payments on people.pid = payments.pid
group by people.pid

Hi Paul,

Next time, it helps to post the CREATE TABLE and INSERT statements to
recreate your table and fill it with some sample data, so that others can
run your query and test what happens. If you get error messages, it also
helps to include them in the message.

In this case, your syntax is wrong. You join the payments table twice,
once with the "old-style" join syntax (from tablename, tablename, ...) and
once with the "new-style" join syntax (from tablename [jointype] join
tablename on conditions ...). For outer joins, only the "new-style" is
safe. Change your query to this:

SELECT people.pID, COALESCE (payments.amount,0)
FROM people -- <-- Change is here!!
left join payments on people.pid = payments.pid
group by people.pid
Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)