Query using Avg order by date

1
votes

I am querying a table with dates and numbers. I need top 10 dates in desc order and a value for the average column.

SELECT TOP (10) columnName1, AVG(columnNumber1) AS AvgNumber
WHERE ....
AND ...

GROUP BY columnName1
**Order by date desc**

to get the top resent dates i need to order by date desc

rgs

i need to sort the date column and then average the top ten. using group by i have to place date in select and get 10 results...i dont want to group by date. this probably not the way

eg HireDate LocationID

2001-01-01 1

2002-08-12 1

1999-09-01 2

1996-03-16 1

2007-05-17 1

2001-11-15 4

2000-01-01 1

2001-11-15 NULL

2003-09-30 2

2004-10-04 2

1989-04-01 1

1995-05-26 4

select top (5) avg(locationid) from Employee order by HireDate desc

Msg 8127, Level 16, State 1, Line 2 Column "Employee.HireDate" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

Results required

HireDate LocationID 2007-05-17 1

2004-10-04 2

2003-09-30 2

2002-08-12 1

2001-11-15 4

Avg =2

2
sqlgroup-by
What is your question? It is not clear from reading what you are missing. You point out that you need top 10 dates, and an average, which your query appears to give you. What else do you want to know? – Greg the Incredulous
i need to sort the date column and then average the top ten. using group by i have to place date in select and get 10 results...i dont want to group by date. this probably not the way – winchmore
It would make your question clearer if you included some sample input and expected output. – Greg the Incredulous

2 Answers

2
votes

To enforce the required logical order of operations, use a derived table: select top 10 rows based on the descending order of the date column, then use the result as a dataset to obtain the average from it:

SELECT AVG(Value)
FROM
(
  SELECT TOP (10) Value
  FROM YourTable
  WHERE ...
  ORDER BY DateColumn DESC
) AS s
;
0
votes

The easiest way to achieve this is to add the date to the group by. If you don't want to return the date then you can wrap your result in another select to remove it, like this:

SELECT AVGLocation 
FROM (SELECT TOP (5) avg(locationid) as AVGLocation, HireDate
    FROM Employee 
    GROUP BY HireDate
    ORDER BY HireDate desc) TableAliasName