best way to write this simple query ( 1 SUM JOIN )

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)

best way to write this simple query ( 1 SUM JOIN )

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2005-05-17 : 17:57:00

This query is going to be hit lots, so I want to make sure I do it efficiently. Im a little rusty so any advice is appreciated. I need "VOTES" to be the count of rows where mediaID matches tblUserVote, and I need points to be the SUM of the points column where mediaID matches.

Thanks again for the help

mike123

CREATE PROCEDURE dbo.select_Media_FrontPage

AS SET NOCOUNT ON

SELECT TOP 10 categoryID, mediaID, '1' as votes, '2' as points FROM tblMedia M

WHERE activeStatus = 1 and getDate() >= dateRelease

ORDER BY datePosted DESC

GO

I have the following table below, each vote is put into a row.

tblUserVote

mediaID, points

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-05-17 : 19:34:49

I think you want something like this :

Select
categoryID, tblMedia.mediaID, Count(*), SUM(points)
FROM
tblMedia

INNER JOIN tblUserVote ON tblUserVote.mediaID = tblMedia.mediaID

GROUP BY categoryID, tblMedia.mediaID, datePosted
ORDER BY datePosted DESC

Damian
Ita erat quando hic adveni.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2005-05-17 : 20:15:57

Hey Damian!

I think thats what Im looking for, however when there are 0 votes in the table I am having a problem as its not bringing back the record.

Any suggestions?

Thanks again for your help :)

mike123

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-05-17 : 20:24:14

Change INNER JOIN to LEFT OUTER JOIN.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2005-05-17 : 22:51:09

Yikes, I seem to be getting a count of 1 when 0 rows exist on the votes column. I am getting this on all 3 rows returned, even tho all should be 0.

Did I mess this up?

Thanks for any assistance again! cheers

mike123

alter PROCEDURE dbo.select_Media_FrontPage

AS SET NOCOUNT ON

SELECT tblMedia.mediaID, categoryID, mediaHeight, mediaWidth, mediaURL, physicalSizeKB, pageName, mediaTitle,
mediaDesc, datePosted,

Count(*) as votes,

--SUM(points) as points
SUM ( CASE WHEN points IS NULL THEN 0 ELSE points END ) as points

FROM tblMedia

LEFT OUTER JOIN tblUserVote ON tblUserVote.mediaID = tblMedia.mediaID

GROUP BY categoryID, tblMedia.mediaID, mediaHeight, mediaWidth, mediaURL, physicalSizeKB, pageName, mediaTitle, mediaDesc, datePosted
ORDER BY datePosted DESC

GO

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-05-17 : 22:59:51

[code]
ALTER PROCEDURE dbo.select_Media_FrontPage

AS

SET NOCOUNT ON

SELECT
tblMedia.mediaID,
categoryID,
mediaHeight,
mediaWidth,
mediaURL,
physicalSizeKB,
pageName,
mediaTitle,
mediaDesc,
datePosted,
COUNT(tblUserVote.mediaID) AS votes,
--SUM(points) as points
SUM (
CASE
WHEN points IS NULL
THEN 0
ELSE points
END ) as points
FROM
tblMedia
LEFT OUTER JOIN tblUserVote ON tblUserVote.mediaID = tblMedia.mediaID
GROUP BY
categoryID,
tblMedia.mediaID,
mediaHeight,
mediaWidth,
mediaURL,
physicalSizeKB,
pageName,
mediaTitle,
mediaDesc,
datePosted
ORDER BY
datePosted DESC

[/code]

Try to think about why your code is doing what it's doing here.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2005-05-17 : 23:33:36

thanks a bunch derrick :)

mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources