Incorrect query results, could use fresh eyes

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

General SQL Server Forums

New to SQL Server Programming

Incorrect query results, could use fresh eyes

Author

Topic

myksdsu
Starting Member

13 Posts

Posted - 2007-06-04 : 15:58:41

I have three tables:

Category: category id, category name, more…
Topic: topic id, topic name, category id, more…
Post: post id, post text, topic id, more…

I need help with a query to display the following:

Category name, # of topics, # of posts

Example:
Category.........................Topics.....Posts
SQL Stored Procedures.........12........562

It’s coming along but there are some problems, ASP.NET actually has 2 posts not 1. And the java totals are correct but it should be Java, 3, 10 (all in one line)
Category.....Topics...Posts
ASP.NET.........2........1
C#................1........1
Java..............1........1
Java..............1........2
Java..............1........7

Overview: use category id to get count of topics then use the topic id to get the count of posts.

SELECT     C.CategoryName, T.ThreadCount AS Threads, T.PostCount AS Posts
FROM        Category AS C LEFT OUTER JOIN
                  (SELECT   tt.CategoryID, PostID.PostCount, COUNT(tt.ThreadName) AS ThreadCount
                   FROM     Thread AS tt LEFT OUTER JOIN
                            (SELECT ThreadID, COUNT(PostID) AS PostCount
                                 FROM   Post AS P
                                 GROUP BY ThreadID) AS PostID 
                                 ON tt.ThreadID = PostID.ThreadID
                              GROUP BY tt.CategoryID, PostID.PostCount) AS T 
                              ON C.CategoryID = T.CategoryID
WHERE     (C.CategoryID = T.CategoryID)
GROUP BY C.CategoryName, T.ThreadCount, T.PostCount
ORDER BY C.CategoryName

Thanks in advance

sagi
Starting Member

1 Post

Posted - 2007-06-04 : 16:19:35

This is happening because you are using T.ThreadCount, T.PostCount in your GROUP BY. remove them from there and it will show your results...

SELECT C.CategoryName, T.ThreadCount AS Threads, T.PostCount AS Posts
FROM Category AS C LEFT OUTER JOIN
(SELECT tt.CategoryID, PostID.PostCount, COUNT(tt.ThreadName) AS ThreadCount
FROM Thread AS tt LEFT OUTER JOIN
(SELECT ThreadID, COUNT(PostID) AS PostCount
FROM Post AS P
GROUP BY ThreadID) AS PostID
ON tt.ThreadID = PostID.ThreadID
GROUP BY tt.CategoryID, PostID.PostCount) AS T
ON C.CategoryID = T.CategoryID
WHERE (C.CategoryID = T.CategoryID)
GROUP BY C.CategoryName
ORDER BY C.CategoryName

pootle_flump

1064 Posts

Posted - 2007-06-04 : 16:47:24

[code]SELECT c.CategoryName
, TheCounts.PostCount
, TheCounts.ThreadCount
FROM Category AS c
LEFT OUTER JOIN--Count of Threads & Posts
(SELECT t.CategoryID
, COUNT(p.PostID) as PostCount
, COUNT(*) AS ThreadCount
FROM dbo.Thread AS t
LEFT OUTER JOIN
dbo.Post AS p
ON p.ThreadID = t.ThreadID
GROUP BY t.CategoryID) AS TheCounts
ON TheCounts.CategoryID = c.CategoryID
ORDER BY c.CategoryName
[/code]

myksdsu
Starting Member

13 Posts

Posted - 2007-06-04 : 16:54:10

Thank you for your patience and help it is much appreciated!!!

Pootle that solution worked great. I will spend some time and understand what you have shown me.

pootle_flump

1064 Posts

Posted - 2007-06-04 : 16:55:33

quote:
Originally posted by sagi

This is happening because you are using T.ThreadCount, T.PostCount in your GROUP BY. remove them from there and it will show your results...

I am afraid you will find that it will fail

Short of a rejig (a la my query), the problem is with the grouping rather than summing of PostID.PostCount in the middle query. The group by in the outer query is just plain old unnecessary. As is the where clause too.

pootle_flump

1064 Posts

Posted - 2007-06-04 : 16:56:28

quote:
Originally posted by myksdsu

When I remove T.ThreadCount or T.PostCount from the group by, it says that T.ThreadCount and T.PostCount are invalid because they are not contained in either an aggregate function or Group by clause. any ideas on overcoming this?

pootle_flump

1064 Posts

Posted - 2007-06-04 : 17:08:57

quote:
Originally posted by myksdsu

Thank you for your patience and help it is much appreciated!!!

Pootle that solution worked great. I will spend some time and understand what you have shown me.

Lol - no probs. My sniped piccy looks a bit odd now!

You don't actually need a derived table. Figure out how to get rid of it and you will know you've cracked it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources