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?
6 1418
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
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.
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
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.
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
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Mark |
last post by:
I am having a problem getting my resultset to come out right. I am
looking to join 3 tables getting a sum out of two and data out of the
the third:
Tables:
users
debits
credits
These...
|
by: U N Me |
last post by:
I have a continuous form that lists records from a query. One of the
columns is an amount field, In the footer of the form I place a text
box that displays the total (sum) of the amount.
The...
|
by: richard |
last post by:
I have a table with 5 fields into which the user will enter numbers,
which must add up to 100%. Say the fields are Toyotas, Missans, Mazdas,
Hondas and Other. I am having trouble writing a query...
|
by: Dirk Goossens |
last post by:
Hello
Is there an alternative for the Excel-function "SumIF" in Access?
Why?
I have one table with profits and expenses.
First field, description (text): indication like "Expense" or...
|
by: Dirk Goossens |
last post by:
Hello
Is there an alternative for the Excel-function "SumIF" in Access?
Why?
I have one table with profits and expenses.
First field, description (text): indication like "Expense" or...
|
by: Paddy |
last post by:
I was browsing the Voidspace blog item on "Flattening Lists", and
followed up on the use of sum to do the flattening.
A solution was:
I would not have thought of using sum in this way. When...
|
by: lawton |
last post by:
Source: this is an access 2003 question
My knowledge level: reading books, internet, and trial & error; no formal training
I'm trying to get a running sum of what's filtered in a subform which is...
|
by: bearophileHUGS |
last post by:
Empty Python lists don't know the type of the items it will
contain, so this sounds strange:
0
Because that may be an empty sequence of someobject:
0
In a statically typed language in...
|
by: azegurb |
last post by:
Hello, I have one question again
i created one table again and in this table i added some another options
for ex at the previous table there were only one problem sum of the dynamically added...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |