Recursive query question - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Recursive query question

Author

Topic

JohnBGood
Starting Member

48 Posts

Posted - 2009-10-03 : 10:26:08

I want to do some grouping and summing. My data looks like so...

DECLARE @foo TABLE
(
[TimeByDay] DateTime
,Group1 NVarChar(55)
,Group2 NVarChar(55)
,Cost Money
)

INSERT @foo
SELECT 1/1/2001, 'A1','T1', 5
UNION SELECT 1/1/2001, 'A2','T1', 1
UNION SELECT 1/2/2001, 'A1','T1', 3
UNION SELECT 1/2/2001, 'A1','T1', 2
UNION SELECT 1/3/2001, 'A1','T1', 2
UNION SELECT 1/3/2001, 'A2','T1', 4
UNION SELECT 1/4/2001, 'A1','T1', 1
UNION SELECT 1/4/2001, 'A2','T1', 1

-- I'm trying to return results that look like this
-- 1/1/2001,'T1', 6
-- 1/2/2001, 'T1', 5
-- 1/3/2001,'T1', 6
-- 1/4/2001,'T1', 2

-- and what I've done so far doesnt work worth a crap

;with CTE as
(
select
TimeByDay
,Group1
,Group2
,Cost

from @Foo f1

Union All
Select
f1.TimeByDay
,f1.Group1
,f2.Group2
,f2.Cost
From
@Foo f2
Join CTE f1 on f1.Group2 = f2.Group2
)

select
TimeByDay

,Group2
,Sum(Cost)
from
CTE
Group By
TimeByDay

,Group2
OPTION (MAXRECURSION 0)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-03 : 10:31:47

is this what you want ?


select	TimeByDay, Group2, sum(Cost)
from	@foo
group by TimeByDay, Group2

KH
[spoiler]Time is always against us[/spoiler]

JohnBGood
Starting Member

48 Posts

Posted - 2009-10-03 : 16:09:34

oops... i'm afraid i made an error in the question... i will re -ask it. Thanks for the right answer to my wrong question.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources