[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
9
Medium Priority
?
543 Views
Last Modified: 2013-12-09
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' ";

Open in new window


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

Accepted Solution

by:
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

by:rmmarsh
ID: 37034338
Perfect...thanks Nick
0
 

Author Comment

by:rmmarsh
ID: 37034469
Oops... doesn't compute on all of the qualified rows...
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 19

Expert Comment

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

Author Comment

by:rmmarsh
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'

Open in new window

0
 

Author Comment

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

Author Comment

by:rmmarsh
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

Open in new window

0
 

Author Comment

by:rmmarsh
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

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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question