How to correctly compute a value based on two fields
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:
Is there a way I can have Firebird do the computation for Cost, taking into consideration the Quantity also (multiply Cost by Quantity)?
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)?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Oops... doesn't compute on all of the qualified rows...
what exactly do you mean by "doesn't compute"
ASKER
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:
Here is what I have:
select sum(Price * NbrOfCopies) as totalValue from tBooks where Stat = 'For Sale'ASKER
I tried doing a "cast(NbrOfCopies as integer)", but it didn't like that either...
ASKER
Oh, one more question:
Can I do something like this without screwing up the existing data?
Can I do something like this without screwing up the existing data?
ALTER TABLE tBooks ALTER NbrOfCopies TYPE integerASKER
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?
ASKER
Please ignore the above questions... opening a new question...
ASKER