Solved

sql query top 10percentage  values

Posted on 2011-03-09
19
354 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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 500 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

734 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