subsum -- sql server mseq

u50415039 NO[at]SPAM spawnkill.ip-mobilphone.net
9/1/2003 3:27:59 AM

#temptable (order, name, activity and count):

1 mark1 null 0
1 mark1 movie 5
1 mark1 game 4
1 mark1 game 3
2 mark2 null 0
2 mark2 sports 6
2 mark2 game 4
2 mark2 game 3
2 mark2 candy 2
3 mark3 null 0
3 mark3 movie 2
3 mark3 game 4
3 mark3 candy 3

from #temptable (above),
how to create #temptable1(below)?.
(adding same activities within an order):

1 mark1 movie 5
1 mark1 game 7
2 mark2 sports 6
2 mark2 game 7
2 mark2 candy 2
3 mark3 movie 2
3 mark3 game 4
3 mark3 candy 3

--
Sent by ricksql from yahoo subpart from com
This is a spam protected message. Please answer with reference header.

Uri Dimant
9/1/2003 7:10:35 AM

Hi,
I have just added PK to your table. Try to avoid using reserve words like
order ,name etc.

CREATE TABLE #TEMP
(
[ID] int not null primary key,
[order] INT NOT NULL,
[name] VARCHAR(10) NOT NULL,
activity VARCHAR(10),
[count] INT NOT NULL
)

INSERT INTO #TEMP values (1,1,'mark1',NULL,0)
INSERT INTO #TEMP values(2,1,'mark1','movie',5)
INSERT INTO #TEMP values(3,1,'mark1','movie',4)
INSERT INTO #TEMP values(4,1,'mark1','game',3)

INSERT INTO #TEMP values(5,2,'mark2',NULL,0)
INSERT INTO #TEMP values(6,2,'mark2','sport',3)
INSERT INTO #TEMP values(7,2,'mark2','game',2)
INSERT INTO #TEMP values(8,2,'mark2','game',1)

SELECT * FROM #TEMP

SELECT [order],[name],activity,sum([count])
FROM #TEMP where activity is not null
group by [order],[name],activity

drop table #temp
[quoted text, click to view]

sql server mseq : subsum