Group on a single item - newbie qeustion

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)

Group on a single item - newbie qeustion

Author

Topic

JohnBGood
Starting Member

48 Posts

Posted - 2009-10-05 : 15:04:44

-- I have the following query

DECLARE @foo TABLE (
[ID] INT
,AssID int
,[TimeByDay] datetime
, [TaskName] NVARCHAR(255)
, [ParentId] INT
, [cost] MONEY
)

INSERT @foo
SELECT 1,null,null,'Design Activities', null, 0
UNION SELECT 2,null,null, 'Write Specs', 1, 0
UNION SELECT 3,10,'1/1/2009', 'Write UI Spec', 2, 5
UNION SELECT 3,10,'1/2/2009', 'Write UI Spec', 2, 5
UNION SELECT 3,10,'1/3/2009', 'Write UI Spec', 2, 5
UNION SELECT 3,10,'1/4/2009', 'Write UI Spec', 2, 5
UNION SELECT 4,11,'1/1/2009', 'Write Data Spec', 2, 5
UNION SELECT 4,11,'1/2/2009', 'Write Data Spec', 2, 5
UNION SELECT 5,null,null, 'Build Prototypes', 1, 0
UNION SELECT 6,12,'1/1/2009', 'Build Screen Mockkups', 5, 5
UNION SELECT 6,12,'1/2/2009', 'Build Screen Mockkups', 5, 5

select TimeByDay, sum(cost) from @foo where timebyDay is not null
group by timebyDay

/* this query returns the results that I want, specifically:

2009-01-01, 15.00
2009-01-02, 15.00
2009-01-03, 5.00
2009-01-04, 5.00

However, if I add a second column to my select statement, I'm no longer grouping on "TimeByDay" like I need.
For example, if I do this...

select TimeByDay, TaskName, sum(cost) from @foo where timebyDay is not null
group by timebyDay, TaskName

... I get this

2009-01-01 Build Screen Mockkups 5.00
2009-01-02 Build Screen Mockkups 5.00
2009-01-01 Write Data Spec 5.00
2009-01-02 Write Data Spec 5.00
2009-01-01 Write UI Spec 5.00
2009-01-02 Write UI Spec 5.00
2009-01-03 Write UI Spec 5.00
2009-01-04 Write UI Spec 5.00

*/

-- how do i select multiple fields, but still get the grouping on "TimeByDay" that I want?????

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-10-05 : 15:33:31

If you want to add TaskName, how do you want the results displayed?

JohnBGood
Starting Member

48 Posts

Posted - 2009-10-05 : 15:48:26

I would prefer to have it like this... I need to add taskname because im passing it as a parameter somewhere else.

2009-01-01, 15.00
2009-01-02, 15.00
2009-01-03, 5.00
2009-01-04, 5.00

JohnBGood
Starting Member

48 Posts

Posted - 2009-10-05 : 15:49:38

actually its not task Name that I need to pass - its the 2 ID fields, however I still need the results to look like

2009-01-01, 15.00
2009-01-02, 15.00
2009-01-03, 5.00
2009-01-04, 5.00

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-10-05 : 16:29:53

I'm still not sure what you want the output to look line since you've just shown me the output you already have.. But two things come to mind:

1) You can use WITH ROLLUP:

select TimeByDay, TaskName, sum(cost) from @foo where timebyDay is not null
group by timebyDay, TaskName
WITH ROLLUP

2) Join to the total (or use Cross Apply):

SELECT F.TimeByDay, F.TaskName, T.SumCost
FROM @Foo AS F
INNER JOIN
	(
		select TimeByDay, sum(cost) AS SumCost 
		from @foo 
		where timebyDay is not null
		group by timebyDay
	) AS T
ON T.TimeByDay = F.TimeByDay
where F.timebyDay is not null

JohnBGood
Starting Member

48 Posts

Posted - 2009-10-06 : 08:10:18

Thanks Lamprey. This looks promising, however, i still get 8 rows of results. Im trying to get the cost to roll up for each individual TimeByDay entry. What do you think?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources