Sum() is making problem

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:

Expand|Select|Wrap|Line Numbers

 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

Quote:

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

Expand|Select|Wrap|Line Numbers

 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

Quote:

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

Expand|Select|Wrap|Line Numbers

 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

Expand|Select|Wrap|Line Numbers

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

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

Expand|Select|Wrap|Line Numbers

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

tariquetuku

Quote:

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

Expand|Select|Wrap|Line Numbers

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

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

Expand|Select|Wrap|Line Numbers

(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.

Sum() is making problem

re: Sum() is making problem

re: Sum() is making problem

re: Sum() is making problem

re: Sum() is making problem

re: Sum() is making problem

re: Sum() is making problem

Similar MySQL Database bytes

/bytes/development

/bytes/it

/bytes/about