[tariquetuku]

i have a table called bill, in which there r three fields named bill,paid,exempted.
when i when i want to deduct the due of each customer the following query does not work:

select customer.cc as ccc,
 customer.name as cname,sum(bill.bill) as bill,
 sum(bill.paid) as paid,
 sum(bill)-(sum(paid)+sum(exempted)) as due
 from customer,bill where customer.cc=bill.cc  group by customer.cc

it does not return any result in the 'due' column(just blank) when sum(exempted) is 0.

Whats the problem? Can anyone say?

How to get rid from it?

[code green]

select customer.cc as ccc,
customer.name as cname,sum(bill.bill) as bill,
sum(bill.paid) as paid,
sum(bill)-(sum(paid)+sum(exempted)) as due
from customer,bill where customer.cc=bill.cc group by customer.cc

I think it is because you have defined bill and paid as alias and this is what you are trying to sum
Try

select customer.cc as ccc,
 customer.name as cname,sum(bill.bill) as bill,
 sum(bill.paid) as paid,
 bill-paid+sum(exempted) as due
 from customer,bill where customer.cc=bill.cc  group by customer.cc

[tariquetuku]

Originally Posted by code green

I think it is because you have defined bill and paid as alias and this is what you are trying to sum
Try

select customer.cc as ccc,
 customer.name as cname,sum(bill.bill) as bill,
 sum(bill.paid) as paid,
 bill-paid+sum(exempted) as due
 from customer,bill where customer.cc=bill.cc  group by customer.cc

Dear code green,

It does not work.

[code green]

Sorry, I was talking rubbish.
Your syntax looks correct.
It might be the bracket arrangement
The brackets are not required mathematically

sum(bill)-sum(paid)+sum(exempted)

But I can't see that causing a problem.
Maybe the whole equation needs bracketting

(sum(bill)-sum(paid)+sum(exempted)) AS due

[tariquetuku]

Originally Posted by code green

Sorry, I was talking rubbish.
Your syntax looks correct.
It might be the bracket arrangement
The brackets are not required mathematically

sum(bill)-sum(paid)+sum(exempted)

But I can't see that causing a problem.
Maybe the whole equation needs bracketting

(sum(bill)-sum(paid)+sum(exempted)) AS due

Dear code green,

it does not work also.

[code green]

It is really frustrating when posters use the term 'does not work' as this is very unhelpful.
What results are you getting?
What results do you expect?
What data is present in the relevant tables?

The syntax looks correct to me unless I am missing something.
I am beginning to suspect the data

[coolsti]

Please post the output from "show create table bill" and "show create table customer", and maybe post some example data that is giving you problems.

This would let someone like myself create the tables quickly and try the query to see what the problem might be.