April 20, 2010 at 6:04 am
hi friends
i want to return all rows in MyTable and sum of all num fields:
WITH MyTable as
(
SELECTtop(100) Link,COUNT(*) as num,SUM(click) as click,SUM(vote)as vote FROM
Recent
Group by Link
Order by num desc
)
select SUM(num) from MyTable
select * from MyTable
how can i join 2 select command's results together?
April 20, 2010 at 6:12 am
You could use a subquery:
WITH MyTable AS
(
SELECT TOP(100) Link,COUNT(*) AS num,SUM(click) AS click,SUM(vote)AS vote FROM
Recent
GROUP BY Link
ORDER BY num DESC
)
SELECT *, NUM = (SELECT SUM(num) FROM MyTable)
FROM MyTable
-- Gianluca Sartori
April 20, 2010 at 6:18 am
You could also use a windowed aggregate with a slight trick:
DECLARE @Recent TABLE (
Link varchar(500),
click int,
vote int
)
INSERT INTO @Recent VALUES('google',1,5)
INSERT INTO @Recent VALUES('google',2,3)
INSERT INTO @Recent VALUES('google',1,2)
INSERT INTO @Recent VALUES('yahoo',3,1)
INSERT INTO @Recent VALUES('yahoo',9,3)
INSERT INTO @Recent VALUES('ssc',1,7)
;WITH MyTable AS
(
SELECT TOP(100) Link,COUNT(*) AS num,SUM(click) AS click,SUM(vote)AS vote
FROM @Recent
GROUP BY Link
ORDER BY num DESC
)
SELECT *, NUM = SUM(num) OVER (PARTITION BY (SELECT NULL))
FROM MyTable
-- Gianluca Sartori
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply