Solved

sql query top 10percentage  values

Posted on 2011-03-09
19
352 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:ewangoya
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

809 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