sql query top 10percentage values

Hi, i have the query below which i need to modify to get the 10 highest percentage values of profit out of my table
...(val - cost) / val

im getting divide by zero errors when i try to add " /val"
 to the query below

and im only getting the 10 latest entries by using "TOP 10"

How would i get the 10 highest profit percentage values?

cheers
SELECT TOP 10 product,val,cost,(val - cost) AS profit
FROM scheme.opsahistm
ORDER BY val DESC

Open in new window

LVL 1
joethermalAsked:
Who is Participating?
 
appariCommented:
no it returns proper results,
Checked with sample data.
declare @Example table(product char(3),
val numeric(5,0),cost numeric(5,0))

Insert into @Example 
Select 'aa',24,12
union
Select 'bb',48,12
union
Select 'cc',28,21
union
Select 'dd',12,8
union
Select 'ee',60,40
union
Select 'ff',40,10
union
Select 'gg',100,90
union
Select 'hh',30,15
union
Select 'ii',400,300
union
Select 'jj',20,10
union
Select 'kk',60,10
union
Select 'll',50,10
union
Select 'mm',88,40
union
Select 'nn',198,20
union
Select 'oo',450,30
union
Select 'pp',22,11

Select *,(val - cost)/val AS profit from @Example 

SELECT TOP 10 product,val,cost,(val - cost)/val AS profit
FROM @Example
ORDER BY profit DESC

Open in new window


eeOut.JPG
0
 
appariCommented:
either ignore records with Val=0 or decide what you want to show as profit in case val is 0

SELECT TOP 10 product,val,cost,(val - cost)/val AS profit
FROM scheme.opsahistm
where val<> 0
ORDER BY val DESC

or

SELECT TOP 10 product,val,cost,(val - cost)/ case when val<>0 then val else 1 end AS profit
FROM scheme.opsahistm
ORDER BY val DESC
0
 
Ephraim WangoyaCommented:
try

declare @Total float

select @total = sum(cost) from scheme.opsahistm

SELECT TOP 10 product,val,cost,(val - cost) AS profit, (val-cost)/@Total*100 as Percentage
FROM scheme.opsahistm
ORDER BY val DESC
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Paul JacksonSoftware EngineerCommented:
try :
SELECT TOP (10) PERCENT WITH TIES product,val,cost,(val - cost) AS profit
FROM scheme.opsahistm
ORDER BY (val-cost) DESC
0
 
joethermalAuthor Commented:
Thanks appari, im not getting any divide by zero errors

, but im still only getting the first 10 results with "TOP 10" ...i need to get the 10 highest percent values from all the rows in the table
0
 
Paul JacksonSoftware EngineerCommented:
You need to use the PERCENT keyword as in my example
0
 
appariCommented:
try

SELECT TOP 10 product,val,cost,(val - cost)/val AS profit
FROM scheme.opsahistm
ORDER BY profit DESC

0
 
joethermalAuthor Commented:
thanks ewangoya, i am getting this error (in MS SQL Server management studio express) ...but i will also need to code this in php

Msg 137, Level 15, State 1, Line 3
Must declare the scalar variable "@total".
0
 
joethermalAuthor Commented:
thanks jacko72, but for some reason your query is returning 5042 rows results
0
 
joethermalAuthor Commented:
appari , thanks but...that will only change the sort order of the "TOP 10" results ..i need the 10 highest profit  percent values
0
 
appariCommented:
they are the records with top 10 profit percent values.

or explain with sample data and the result you want.
0
 
Paul JacksonSoftware EngineerCommented:
try it without the parenthesis around the 10:

SELECT TOP 10 PERCENT WITH TIES product,val,cost,(val - cost) AS profit
FROM scheme.opsahistm
ORDER BY (val-cost) DESC
0
 
Paul JacksonSoftware EngineerCommented:
good point appari, how many records are there in total?
0
 
Paul JacksonSoftware EngineerCommented:
think you need a combination of mine and appari's try:

SELECT TOP 10 PERCENT product,val,cost,(val - cost)/val AS profit
FROM scheme.opsahistm
ORDER BY profit DESC
0
 
Paul JacksonSoftware EngineerCommented:
forgot with ties

SELECT TOP 10 PERCENT WITH TIES product,val,cost,(val - cost)/val AS profit
FROM scheme.opsahistm
ORDER BY profit DESC
0
 
joethermalAuthor Commented:
thanks for your help so far, in the attached pic i have an example table ...the blue highlite shows the ten rows that will be returned if i use "TOP 10" ...correct me if i am wrong

on the right is what i want returned ...10 rows, containing the highest profit percentage from the whole table(which is 50,000 rows) ..
jacko72 ....that is probably why 5402 rows are returned using "TOP 10 PERCENT"

note: the table does not have a column with the profit percent ...this has to be worked out in the query

i hope this makes things clearer example
0
 
appariCommented:
then this query should return you proper results, notice the order by is on the calculated profit column. it orders the result by the calculated profit value and returns top 10 results.

SELECT TOP 10 product,val,cost,(val - cost)/val AS profit
FROM scheme.opsahistm
ORDER BY profit DESC
0
 
joethermalAuthor Commented:
appari, your query with TOP 10 will take the first 10 rows from the table, calculate the profit percentage and then order them by profit ...that is not what i want

i want to look through all of the rows (not the first ten)
and get the ten rows with the highest profit (from all of the rows in the table)

check out the example again ...your query will return the rows highlited in blue ...i want the ten rows on the right

cheers
exam.gif
0
 
joethermalAuthor Commented:
you are right, this does solve my problem

thank you very much for perservering
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.