help with join / sum - 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 2000 Forums

Transact-SQL (2000)

help with join / sum

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-09-05 : 00:28:15

Im having some problems with the stored proc below. I'm not sure if what Im trying to do is possible the way I am approaching it.

Basically I select all the appropriate records in tblzones, then I want the sum of each row for the clicks and views field in the tblstats table. Example I might have 30 rows of 10 clicks. I want to bring back 300 clicks as total clicks.

Thanks for any help

Mike123

alter PROCEDURE [select_tblStats_inventory]
(
@userID int
)

AS SET NOCOUNT ON

SELECT tblZones.zoneID, description, (select sum(tblStats.clicks) as totalClicks), (select sum(tblStats.pageViews) as totalPageViews) FROM tblZones JOIN tblStats on tblStats.zoneID = tblZones.zoneID WHERE userID = @userID

GO

mr_mist
Grunnio

1870 Posts

Posted - 2003-09-05 : 03:27:45

Something like

SELECT
tblZones.zoneID, description, isnull (totalclicks,0) as totalclicks, isnull (totalviews,0) as totalviews
FROM
tblZones
LEFT OUTER JOIN
(SELECT zoneid, sum (clicks) as totalclicks FROM tblstats group by zoneid) clicks
on clicks.zoneid = tblzones.zoneid
LEFT OUTER JOIN
(SELECT zoneid, sum (pageviews) as totalviews FROM tblstats group by zoneid) views
on views.zoneid = tblzones.zoneid

Should do it. Or you could probably combine the two subqueries to make it even simpler. This assumes that you can join on zoneid, obviously. I had to guess this as there is no DDL.

-------
Moo. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources