455,364 Members | 1,343 Online
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
4 Replies

 Expert 100+ P: 1,134 is this what you need Expand|Select|Wrap|Line Numbers SELECT [item id], [unit id],         sum(case when moduletype=0 then price else 0 end) as CostPrice,        sum(case when moduletype=1 then price else 0 end) as PurchasePrice FROM YourTable GROUP BY [item id], [unit id]   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 update table t1, table t2 set t1.unitid = t2.unitid where t2.moduletype = '0' --cost and t1.moduletype = '1' --purchase and t1.price <> t2.price   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 update table t1, table t2 set t1.unitid = t2.unitid where t2.moduletype = '0' --cost and t1.moduletype = '1' --purchase and t1.price <> t2.price   May 1 '08 #5