[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# How to correctly compute a value based on two fields

Posted on 2011-10-26
Medium Priority
543 Views
I have a table with items for sale, along with the quantity for each item.  I am trying to correctly come up with accounting statistics, but so far I am unable to take into consideration the quantity when computing total value.

This is what I have right now, which is clearly not correct:

``````selectStatement = "select sum(Cost) from tBooks where Stat = 'For Sale' ";
``````

Is there a way I can have Firebird do the computation for Cost, taking into consideration the Quantity also (multiply Cost by Quantity)?
0
Question by:rmmarsh
• 7
• 2

LVL 19

Accepted Solution

NickUpson earned 2000 total points
ID: 37033040
is this ok:

select sum(Cost * quantity) as totalvalue from tBooks where Stat = 'For Sale
0

Author Closing Comment

ID: 37034338
Perfect...thanks Nick
0

Author Comment

ID: 37034469
Oops... doesn't compute on all of the qualified rows...
0

LVL 19

Expert Comment

ID: 37036224
what exactly do you mean by "doesn't compute"
0

Author Comment

ID: 37038270
Turns out I was using the wrong field for Quantity... this is what it's supposed to be, however I originally defined NbrOfCopies as varchar(3) and now it's causing me problems...

Here is what I have:

``````select sum(Price * NbrOfCopies) as totalValue from tBooks where Stat = 'For Sale'
``````
0

Author Comment

ID: 37038302
I tried doing a "cast(NbrOfCopies as integer)", but it didn't like that either...
0

Author Comment

ID: 37038335
Oh, one more question:

Can I do something like this without screwing up the existing data?

``````ALTER TABLE tBooks ALTER NbrOfCopies TYPE integer
``````
0

Author Comment

ID: 37038546
Well, I found out that I can't do ALTER TABLE as above... is there some way I can define a new column of type integer and move the data from NbrOfCopies (which is varchar (3) to the new column?
0

Author Comment

ID: 37038551
Please ignore the above questions... opening a new question...
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance andâ€¦
In this blog post, weâ€™ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filledâ€¦
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In thisâ€¦
###### Suggested Courses
Course of the Month18 days, 18 hours left to enroll