sort grouped results of aggregate functions 1

[theotrain]

in SQL like this:
[tt]
select AVG(column1), rowID
from myTable
where rowID = 5
group by rowID
[/tt]
the actual query is a big mess so im just trying to show the essential elements here. what i have is an aggregate function and some other row that i am grouping by in the select statement.

in addition to this i want to SORT BY a date, lets say "myDate". each row in this table has a unique myDate, so it seems like SQL is choking on the idea. it only wants aggregate functions and the thing you are grouping by in the select statement.

so what is the workaround to sorting the results after grouping them? i realize the functions are mashing rows together so there is no obvious myDate to sort by, it would have to pick one or know how to pick one... but can anybody offer a possibility for sorting these kinds of results by date, when the date is not the same across each of the grouped rows in the result set? i could take the first date in the group, or the last, doesnt matter, i just need something that will work someway

thanks!

 

[PWise]

can you provide some sample data and desierd result

 

[dhookom]

Assuming you are working with the Orders table in Northwind and you want to Average the Freight by Customer and Order By the min of OrderDate, your SQL would be:

SELECT Avg(Freight) AS AvgFreight, CustomerID
FROM Orders
GROUP BY CustomerID
ORDER BY Min(OrderDate);

Duane
Hook'D on Access
MS Access MVP

 

[PWise]

I do understand what is the point of this order by

 

[Golom]

I think that this thread is about the same thing as thread701-1485917. As you said pwise**, it is not apparent what is gained by the ORDER BY.


** I assume that you meant

I do [red]NOT[/red] understand what is the point of this order by

 

[PWise]

Golom:

You Are Right

SB

I do NOT understand what is the point of this order by

 

[theotrain]

dhookum, you nailed it!

thats exactly what i needed. the book i looked at showed MIN as a numerical function so i didnt think it would work with dates. im sure everyone who uses SQL knows better, but i didnt.

the point of the "order by" is that im retrieving a bunch of grouped, averaged test scores and showing them in a cfchart. the tests can technically be taken in any order, but overwhelmingly they will be taken in the same chunks that the "group by" returns. so i want the chart to show, at least roughly, the results of the test averages in the chronological order they were taken so progress can be judged.

 

[lespaul]

two things, first, MIN will work on strings as well as numbers; second, a date is a number!!!

The integer part is the days since 12/30/1899 and the decimal is the time of day:

7/10/2008 9:48 am MDT = 39639.4083333

try it, go to excel in cell A1 type a date and time. Set A2 =A1 and then format it as a number with 7 decimals.



Leslie

Have you met Hardy Heron?