Select statement with group by and sum

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

Select statement with group by and sum

Author

Topic

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2008-12-11 : 03:26:05

below is my table

-----------------------------------------
category userid time_from time_to
_________________________________________

cat1 ck 8 10
cat2 ck 11 18
cat1 ck 12 16

cat3 gg 12 14
cat1 gg 11 15
cat3 gg 17 19

how to is the select statement to generate the query below?

Category UserID Total_Diff count
________________________________________________
cat1 ck 6 ((10-2) + (16-12)) 2
cat2 ck 7 (18-11) 1
cat1 gg 4 (15-11) 1
cat3 gg 4 (14-12) + (19-17) 2

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-11 : 04:52:53

declare @table table (category varchar(24), userid varchar(12),time_from int ,time_to int)
insert into @table select 'cat1', 'ck', 8, 10 union all
select 'cat2', 'ck', 11, 18 union all
select 'cat1', 'ck', 12, 16 union all
select 'cat3', 'gg', 12, 14 union all
select 'cat1', 'gg', 11, 15 union all
select 'cat3', 'gg', 17, 19

select category,userid,sum(time_to - time_from) AS total from @table group by category,userid

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-12-11 : 04:58:29

something like this

DECLARE		@category TABLE (userid VARCHAR(100), time_from INT, time_to INT)

INSERT INTO	@category
			SELECT 'cat1 ck', 8, 10
UNION ALL	SELECT 'cat2 ck', 11, 18
UNION ALL	SELECT 'cat1 ck', 12, 16
UNION ALL	SELECT 'cat3 gg', 12, 14
UNION ALL	SELECT 'cat1 gg', 11, 15
UNION ALL	SELECT 'cat3 gg', 17, 19

SELECT		DISTINCT 
			userid
			, SUM(time_to - time_from) OVER (PARTITION BY userid) AS 'TotalDiff'
			, STUFF(( SELECT '+(' + CONVERT(VARCHAR(10), time_to) + '-' + CONVERT(VARCHAR(10), time_from) + ')'
				FROM @category 
				WHERE userid = C.userid
				FOR XML PATH('')), 1, 1, '') AS 'Path'
			, COUNT(userid) OVER (PARTITION BY userid) AS 'Count'
FROM		@category C

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2008-12-14 : 21:44:33

HI bklr & PeterNeo,

Thanks Both of you.
I get what i want from both of you :)
Thanks :)

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-14 : 23:09:20

Welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources