Adding a new column to a group by - select

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

Adding a new column to a group by - select

Author

Topic

Peter01
Starting Member

16 Posts

Posted - 2015-02-25 : 12:59:29

Hello,
I´m looking to create a select where I sum the daily_return by stock_code, and then I would like to have an additional column containing the most recent "rating" available by date

So if you where to execute the query below, the result would be:
stock1 0.54 3
stock2 0.05 1

Can anyone help please?

here is what I have so far:
____________________________________________

DECLARE @stock_returns TABLE
(
stock_code VARCHAR(10) NOT NULL,
date1 DATE NOT NULL,
daily_return NUMERIC(10, 2) NOT NULL,
rating numeric (6,0) not null);

INSERT INTO @stock_returns(stock_code, date1, daily_return,rating)
VALUES ('stock1', '20140708', 0.51,4),
('stock1', '20140709', 0.003,1),
('stock1', '20140710', 0.005,3),
('stock1', '20140711', 0.006,2),
('stock1', '20140712', 0.002,5),
('stock1', '20140713', 0.007,2),
('stock1', '20140714', 0.0024,7),
('stock1', '20140715', 0.0024,2),
('stock1', '20140717', 0.0025,3),
('stock2', '20140710', 0.0016,5),
('stock2', '20140711', 0.0066,7),
('stock2', '20140712', 0.0023,1),
('stock2', '20140713', 0.0035,3),
('stock2', '20140714', 0.0057,4),
('stock2', '20140715', 0.0097,6),
('stock2', '20140716', 0.0071,2),
('stock2', '20140717', 0.0071,1);

select stock_code, sum(daily_return) as total from @stock_returns
group by stock_code

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-25 : 13:33:10

[code]
with cte1 (stock_code, total)
as
(
select stock_code, sum(daily_return) as total from @stock_returns
group by stock_code
),
cte2 (stock_code, maxdate)
as
(
select stock_code, max(date1) as maxdate from @stock_returns
group by stock_code
)
select cte1.stock_code, cte1.total, sr.rating
from @stock_returns sr
join cte1 on cte1.stock_code = sr.stock_code
join cte2 on cte2.stock_code = sr.stock_code and cte2.maxdate = sr.date1
[/code]

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-02-25 : 16:56:41

Alternative:

select stock_code
      ,total
      ,rating
  from (select stock_code
              ,sum(daily_return) over(partition by stock_code) as total
              ,rating
              ,row_number() over(partition by stock_code order by date1 desc) as rn
          from @stock_returns
       ) as a
 where rn=1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources