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
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.
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.
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
0
radhakrishan1Author 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.
0
Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.
-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
0
radhakrishan1Author 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
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)))
(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)))
0
Featured Post
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!
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