# Sorting on Decimal Value

Have a look at this query :

Select       Id,
sum(Day0) As Day0,
sum(Day0) As Day1,
sum(Day0) As Day2,
sum(Day0) As Day3,
sum(Day0) As Day4,
sum(Total) As Total,
Cast ( (sum(Day0) *100/ sum(Total)) as decimal(18,3)) As Schedule
From tblABCD
Group by  Id
Order by  Schedule DESC

Output which I get :
---------------------------------------------------
01234 58.45% 34.40% 6.99% 0.13% 0.03%
07729 58.35% 41.47% 0.00% 0.00% 0.18%
07774 58.93% 41.07% 0.00% 0.00% 0.00%
07602 58.99% 41.01% 0.00% 0.00% 0.00%
01042 58.86% 33.21% 1.38% 6.51% 0.05%
00148 58.13% 41.81% 0.00% 0.03% 0.03%
----------------------------------------------------

I have run this query in ASP PAGE and how output was this ?
If u look Percentage after IDS (58.45, 58.35, 58.93, 58.99, 58.86, 58.13) they r not sorted.

Can anyone pls help urgently.

###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CTOCommented:
Which columns need to be sorted? Do you want to sort only on 1 column or on multiple columns>

You are currently sorting by Schedule DESC - I don't see a schedule column in your results set (in fact, you have 7 columns in your select statement and 6 in your results set)

To sort by Day0, do this:

Select      Id,
sum(Day0) As Day0,
sum(Day0) As Day1,
sum(Day0) As Day2,
sum(Day0) As Day3,
sum(Day0) As Day4,
sum(Total) As Total,
Cast ( (sum(Day0) *100/ sum(Total)) as decimal(18,3)) As Schedule
From tblABCD
Group by  Id
Order by  sum(Day0) DESC --Add multiple columns and order on which to sort here.

Cheers
Night
Commented:
Select      Id,
sum(Day0) As Day0,
sum(Day0) As Day1,
sum(Day0) As Day2,
sum(Day0) As Day3,
sum(Day0) As Day4,
sum(Total) As Total,
Cast ( (sum(Day0) *100/ sum(Total)) as decimal(18,3)) As Schedule
From tblABCD
Group by  Id
Order by Cast ( (sum(Day0) *100/ sum(Total)) as decimal(18,3)) DESC

Hilaire
Author Commented:
I want to sort out on Schedule which is not column, the value is calculated by dividing values from sum(day0)/sum(total)

Again I don't use Schedule, to display in ASP page, Im just using to sorting it.

Can we sort on just on SCHEDULE.
Commented:
A little bit of explanation

-you can NOT use a column alias to sort results
-you can use the column# of the resultset
eg order by 3 desc sorts on the third column descending
or you can repeat the column definition, as in my previous post

Order by Cast ( (sum(Day0) *100/ sum(Total)) as decimal(18,3)) DESC

HTH

Hilaire
Author Commented:
Hi Hilaire,

Just tried what u said, but it doesn't sort on decimal places. Means all 97% will be together but it wont take in account the decimal places after the number
Commented:
Do the decimal places appear in the schedules col ?

If so, use
"order by n desc"
where n is the place of the schedule col in your query

eg try this simplified version

Select      Id,
Cast ( (sum(Day0) *100/ sum(Total)) as decimal(18,3)) As Schedule
From tblABCD
Group by  Id
Order by  2 DESC

if the decimal places do not appear in the schedule col in QA,
you 'll have to change the query

(cast(sum(Day0) as decimal(18,3)) * 100.00 / cast(sum(Total)) as decimal(18,3))) as schedule
...
order by (cast(sum(Day0) as decimal(18,3)) * 100.00 / cast(sum(Total)) as decimal(18,3)))

Hilaire

Experts Exchange Solution brought to you by