473,320 Members | 2,180 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

SQL query calculation

Can anyone help me?

I have a table which has item id, moduletype, unit id, price field.
I would like to do calculation like this.
For the same item price is different according to moduletype.
I would like to select price as 'cost price' only when moduletype='0'
and price as 'purchase price' if moduletype='1'
I tried to use case statement but it shows me null if condition doesnot satisfy.
I want to show the cost price and purchase price of one item in one row not in two row . SO, case statement doesn't work at this time (i think).

And also if unit id of the cost price doesn't match with the unit price of purchase prise than i need to convert the unit id of purchse price to unit id of costprice. How can i do that?

Thanks in advance
P
Apr 30 '08 #1
4 1641
Delerna
1,134 Expert 1GB
is this what you need
Expand|Select|Wrap|Line Numbers
  1. SELECT [item id], [unit id], 
  2.        sum(case when moduletype=0 then price else 0 end) as CostPrice,
  3.        sum(case when moduletype=1 then price else 0 end) as PurchasePrice
  4. FROM YourTable
  5. GROUP BY [item id], [unit id]
  6.  
Apr 30 '08 #2
deric
92
For your first issue, use a Pivot to achieve that. I believe you can make it on your own. If you will encounter problems with your created code using pivot, let us know.

For the second issue, please rephrase your question. I'm confused whether the unit IDs will not match or the unit prices.
I wrote a code from the way I understand your statement. It is not tested because I dont have a query analyzer here. But, you might get an idea out from it..
Expand|Select|Wrap|Line Numbers
  1. update table t1, table t2
  2. set t1.unitid = t2.unitid
  3. where t2.moduletype = '0' --cost
  4. and t1.moduletype = '1' --purchase
  5. and t1.price <> t2.price
  6.  
Apr 30 '08 #3
Delerna
Thanks for Reply.
May 1 '08 #4
Deric
Thanks for you help.
Your post is helpful but i did this in another way. I found another way that we can select data from same table with two condition. We don't need to do case statement .
Anyway it was good information for me regarding to PIVOT.
Thanks
I will be keep on posting the questions in future.



For your first issue, use a Pivot to achieve that. I believe you can make it on your own. If you will encounter problems with your created code using pivot, let us know.

For the second issue, please rephrase your question. I'm confused whether the unit IDs will not match or the unit prices.
I wrote a code from the way I understand your statement. It is not tested because I dont have a query analyzer here. But, you might get an idea out from it..
Expand|Select|Wrap|Line Numbers
  1. update table t1, table t2
  2. set t1.unitid = t2.unitid
  3. where t2.moduletype = '0' --cost
  4. and t1.moduletype = '1' --purchase
  5. and t1.price <> t2.price
  6.  
May 1 '08 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: DD | last post by:
I have made a form from a query that calculates -=Total I want to add a field Named this field then recalculates the MarkUp and Discount Fields and Total, I can do some code in the report which...
4
by: d.p. | last post by:
Hi all, I'm using MS Access 2003. Bare with me on this description....here's the situation: Imagine insurance, and working out premiums for different insured properties. The rates for calculating...
0
by: leavandor | last post by:
I am trying to design a query that works with a relationship between a Table and a Query. I am comparing a value in the table with a computed value inside the query. The reason for this is that...
14
by: Crimsonwingz | last post by:
Need to calculate a sum based on a number of factors over a period of years. I can use formula ^x for some of it, but need totals to carry over in the sum and have only been able to do this thus...
3
by: martlaco1 | last post by:
Trying to fix a query that (I thought) had worked once upon a time, and I keep getting a Data Type Mismatch error whenever I enter any criteria for an expression using a Mid function. Without the...
5
by: jonm4102 | last post by:
I'm trying to calculate the median of some numerical data. The data can only be found in a query (henceforth query 1) field I previously made, and I would prefer to calculate the median in a new...
4
by: heckstein | last post by:
I am a novice working in Access 2002 trying to run a query I created. I am receiving the error "scaling of decimal value resulted in data trunction", which I have determined is due to this...
4
by: anniebai | last post by:
I have a query named "Tuition Calculation" saved in the database. In the code, the criteria of the query need to be added/modified and be saved for use of another auto-generate report. Is...
14
klarae99
by: klarae99 | last post by:
Hello, I am working on an Access 2003 Database for inventory control. I am setting up a form (frmProducts) for the viewing and adding of product information. I have several tables that this...
3
by: oopsatwork | last post by:
I'm trying to construct an XPath and it is giving me trouble. I have an XML that contains a "Routine". The routine contains "Steps". The steps have different types. Some types of steps have...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.