Sub-Select Aggregation Problem

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

SQL Server Development (2000)

Sub-Select Aggregation Problem

Author

Topic

Blastrix
Posting Yak Master

208 Posts

Posted - 2002-12-19 : 18:08:59

I have a query in which I am attempting to get some statistics broken down by user, and am unable to get the results I want. I have the following:

SELECT
	C.UserID,
	U.Username,
	TotalRevisions = (
		SELECT COUNT(*) 
		FROM tblClassifiedHistory CH INNER JOIN tblClassifieds C2 ON CH.ClassifiedID = C2.ClassifiedID AND C2.UserID = C.UserID
		WHERE CH.RevisionDate >= @StartDate AND CH.RevisionDate < @EndDate
	)
FROM
	tblClassifieds C
	INNER JOIN tblUsers U
		ON C.UserID = U.UserID
GROUP BY
	C.UserID,
	U.Username
ORDER BY
	U.Username

If the text " AND C2.UserID = C.UserID" is in the query, then I get an "Internal SQL Server error" message. If I remove it though, it just gives me a count of all revisions in the time period. How can I get those revisions broken down by userID? There will be a few other sub-select aggregates pulled in this query, but I only showed one to keep it more readable.

Thanks,
Steve

<edit> to fix display </edit>

Edited by - robvolk on 12/19/2002 18:21:15

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-19 : 18:29:25

Can you post some ddl, sample data and sample results.
The syntax looks okay.

By the way what version of sql server are you using?

Edited by - ValterBorges on 12/19/2002 18:37:22

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2002-12-19 : 18:55:51

Are there any errors in the SQL Server error log that correspond to the time that you got this error?

nr
SQLTeam MVY

12543 Posts

Posted - 2002-12-20 : 04:37:06

Do you have the latest service pack?
Try
SELECT
C.UserID,
U.Username,
C3.TotalRevisions
FROM
tblClassifieds C
INNER JOIN tblUsers U
ON C.UserID = U.UserID
INNER JOIN (SELECT UserID, TotalRevisions = COUNT(*)
FROM tblClassifiedHistory CH
INNER JOIN tblClassifieds C2
ON CH.ClassifiedID = C2.ClassifiedID
WHERE CH.RevisionDate >= @StartDate AND CH.RevisionDate < @EndDate
GROUP BY UserID) as C3
ON C3.UserID = C.UserID
GROUP BY
C.UserID,
U.Username
ORDER BY
U.Username

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-20 : 09:31:33

I think Valter is right on -- you can't JOIN in a sub-select like that, you need to put criteria in the WHERE clause.

try:

SELECT
C.UserID,
U.Username,
TotalRevisions = (
SELECT COUNT(*)
FROM tblClassifiedHistory CH INNER JOIN tblClassifieds C2 ON CH.ClassifiedID = C2.ClassifiedID WHERE CH.RevisionDate >= @StartDate AND CH.RevisionDate < @EndDate AND C2.UserID = C.UserID

)
FROM
tblClassifieds C
INNER JOIN tblUsers U
ON C.UserID = U.UserID
GROUP BY
C.UserID,
U.Username
ORDER BY
U.Username

- Jeff

Blastrix
Posting Yak Master

208 Posts

Posted - 2002-12-20 : 13:31:03

Thanks for the responses everyone. Jeff, the solution you suggested hit the nail right on the head. As soon as I moved that part everything started working perfectly.

Is there any type of performance difference doing it this way vs. what nr provided?

Thanks,
Steve

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-20 : 17:17:25

Actually my first thought was that you couldn't use C2.UserID = C.UserID in the sub-select join. However I tried it in SQL 2k and it does work for both cases.

That is why I edited my response to ask what version of SQL Server.

Blastrix can you confirm what version of sql server your using.

Blastrix
Posting Yak Master

208 Posts

Posted - 2002-12-21 : 14:08:15

I am using SQL2K, with all patches applied.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-21 : 14:23:05

If you don't mind posting the ddl.
I would like to see why my test worked and you're query is not working with a join expression that has a reference to a higher level select.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources