?
Solved

setting a return value

Posted on 2005-05-03
4
Medium Priority
?
192 Views
Last Modified: 2010-03-19
I am trying to get a value to return based on a formula - this is my query

select warehouse,product,quantity,date,customer,value ,cost,
(case when cost=0 then margin=100 else (sum( ((value-cost)/cost)*100) as margin) end
from sales
group by warehouse,product,quantity,date,customer,value ,cost

basically I need to find the value of margin - but as sometimes the cost field might have a zero value I need to say margin=100 - however my query shown above gives an error. Any ideas what I am doing wrong?
0
Comment
Question by:sagarh
  • 2
4 Comments
 
LVL 39

Expert Comment

by:appari
ID: 13917306
try like this

select warehouse,product,quantity,date,customer,value ,cost,
(case when cost=0 then 100 else (sum( ((value-cost)/cost)*100) ) end as margin
from sales
group by warehouse,product,quantity,date,customer,value ,cost
0
 
LVL 13

Assisted Solution

by:ispaleny
ispaleny earned 1000 total points
ID: 13917307
select warehouse,product,quantity,date,customer,value ,cost,
(case when sum(cost)=0 then 100 else (sum( ((value-cost)/cost)*100) ) end margin
from sales
group by warehouse,product,quantity,date,customer,value ,cost


I don't knoe if you need GROUP BY so many fields.
0
 
LVL 39

Accepted Solution

by:
appari earned 1000 total points
ID: 13917318

missing ), try this


select warehouse,product,quantity,date,customer,value ,cost,
case when cost=0 then 100 else (sum( ((value-cost)/cost)*100) ) end as margin
from sales
group by warehouse,product,quantity,date,customer,value ,cost
0
 
LVL 12

Expert Comment

by:geotiger
ID: 13917770
If you have NULL values, then you may not catch them by only considering 0. Here is modified version of Appari's:

select warehouse,product,quantity,date,customer,value ,cost,
        case when cost=0 or cost IS NULL then 100
        else (sum( ((value-cost)/cost)*100) ) end as margin
from sales
group by warehouse,product,quantity,date,customer,value ,cost
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

840 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