By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,364 Members | 1,343 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,364 IT Pros & Developers. It's quick & easy.

SQL query calculation

P: 24
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
Share this Question
Share on Google+
4 Replies


Delerna
Expert 100+
P: 1,134
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

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

P: 24
Delerna
Thanks for Reply.
May 1 '08 #4

P: 24
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

Post your reply

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