Problem with Select, Avg() - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem with Select, Avg()

Author

Topic

Archy
Starting Member

1 Post

Posted - 2011-06-03 : 14:37:03

I need help in generating this output with this data.

table1 (Column details with Data format)
Code Price Volume Date
---- ----- ------ ----
'A' 50030000 3017136 5/11/2011
'A' 50030000 3117136 5/12/2011
'A' 50553000 3217136 5/13/2011
'A' 50430000 3317136 5/14/2011
'AA' 17050000 26953694 5/11/2011
'AA' 17070000 26953694 5/12/2011

Output needed like this Please help me with Select query.
colum Price should be selected based on max(date) which is 5/14/2011 for code 'A' and 5/12/2011 for Code 'AA' and the Avg(Volume)

Code Price Avg Volume
---- ----- ------
'A' 50553000 3167136
'AA' 17070000 26953694

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-06-03 : 15:04:01

SELECT t1.Code,t1.Price,t1.Volume
FROM table t1
INNER JOIN

(select Code,Max(date) as Date
from table1
group by Code
) t2
ON t1.Code = t2.code and t1.date = t2.date

or

SELECT select code,price,volume, date
FROM
(select code,price,volume, date
[rownumber] = row_number() over(partition by code order by date desc)
from table1
) t1

where rownumber = 1

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources