Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

sql query top 10percentage  values

Posted on 2011-03-09
19
Medium Priority
?
356 Views
Last Modified: 2012-08-13
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

0
Comment
Question by:joethermal
  • 7
  • 6
  • 5
  • +1
19 Comments
 
LVL 39

Expert Comment

by:appari
ID: 35089901
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
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35089903
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
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 35089919
try :
SELECT TOP (10) PERCENT WITH TIES product,val,cost,(val - cost) AS profit
FROM scheme.opsahistm
ORDER BY (val-cost) DESC
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 1

Author Comment

by:joethermal
ID: 35089992
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
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 35089998
You need to use the PERCENT keyword as in my example
0
 
LVL 39

Expert Comment

by:appari
ID: 35090001
try

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

0
 
LVL 1

Author Comment

by:joethermal
ID: 35090006
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
 
LVL 1

Author Comment

by:joethermal
ID: 35090041
thanks jacko72, but for some reason your query is returning 5042 rows results
0
 
LVL 1

Author Comment

by:joethermal
ID: 35090045
appari , thanks but...that will only change the sort order of the "TOP 10" results ..i need the 10 highest profit  percent values
0
 
LVL 39

Expert Comment

by:appari
ID: 35090080
they are the records with top 10 profit percent values.

or explain with sample data and the result you want.
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 35090081
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
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 35090084
good point appari, how many records are there in total?
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 35090132
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
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 35090148
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
 
LVL 1

Author Comment

by:joethermal
ID: 35090357
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
 
LVL 39

Expert Comment

by:appari
ID: 35090530
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
 
LVL 1

Author Comment

by:joethermal
ID: 35090967
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
 
LVL 39

Accepted Solution

by:
appari earned 2000 total points
ID: 35091092
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
 
LVL 1

Author Closing Comment

by:joethermal
ID: 35091831
you are right, this does solve my problem

thank you very much for perservering
0

Featured Post

Industry Leaders: 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

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

885 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