Query help... driving me crazy!

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 2005 Forums

Transact-SQL (2005)

Query help... driving me crazy!

Author

Topic

jbabco
Starting Member

8 Posts

Posted - 2007-12-04 : 13:37:51

I have a forums db and I'm trying to get aggregate numbers. The Posts table looks like:


PostId  ForumId  ThreadId  PostDate   Username  UserRole
------  -------  --------  ---------  --------  --------
1       1        1         some date  bill      user
2       1        1         some date  james     admin
3       1        2         some date  mary      user 
4       2        3         some date  steve     user
5       2        3         some date  ron       admin

I was hoping to get a query to return this info (threads answered by an admin vs. total threads, grouped by ForumId). Query should only report on threads less than 30 days old, so lets assume all the 'some date' above are less than 30 days old:


ForumId  ThreadsAnsweredByAdmin  TotalThreads
-------  ----------------------  ------------
1        1                       2
2        1                       1

Thanks in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-04 : 13:42:25

select forumid,
sum(case when userrole = 'admin' then 1 else 0 end),
count(distinct thredid)
from posts
group by forumid

E 12°55'05.25"
N 56°04'39.16"

jbabco
Starting Member

8 Posts

Posted - 2007-12-04 : 16:14:39

This is close, but consider the scenario where we have more than 1 post by an admin in a particular thread:


PostId  ForumId  ThreadId  PostDate   Username  UserRole
------  -------  --------  ---------  --------  --------
1       1        1         some date  bill      user
2       1        1         some date  james     admin
3       1        2         some date  mary      user 
4       2        3         some date  steve     user
5       2        3         some date  ron       admin
6       1        1         some date  bill      user
7       1        1         some date  james     admin
8       1        1         some date  ron       admin

We now end up with a ThreadAnsweredByAdmin count that exceeds TotalThreads, which is problematic:


ForumId  ThreadsAnsweredByAdmin  TotalThreads
-------  ----------------------  ------------
1        3                       2
2        1                       1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-04 : 16:31:02

[code]DECLARE @Sample TABLE (PostId INT, ForumId INT, ThreadId INT, PostDate VARCHAR(9), Username VARCHAR(5), UserRole VARCHAR(5))

INSERT @Sample
SELECT 1, 1, 1, 'some date', 'bill', 'user' UNION ALL
SELECT 2, 1, 1, 'some date', 'james', 'admin' UNION ALL
SELECT 3, 1, 2, 'some date', 'mary', 'user' UNION ALL
SELECT 4, 2, 3, 'some date', 'steve', 'user' UNION ALL
SELECT 5, 2, 3, 'some date', 'ron', 'admin' UNION ALL
SELECT 6, 1, 1, 'some date', 'bill', 'user' UNION ALL
SELECT 7, 1, 1, 'some date', 'james', 'admin' UNION ALL
SELECT 8, 1, 1, 'some date', 'ron', 'admin'

SELECT ForumId,
COUNT(DISTINCT Admin) AS ThreadsAnsweredByAdmin,
COUNT(DISTINCT ThreadId) AS TotalThreads
FROM (
SELECT ForumId,
CASE
WHEN UserRole = 'admin' then 1
ELSE NULL
END AS Admin,
ThreadId
FROM @Sample
) AS q
GROUP BY ForumId[/code]

E 12°55'05.25"
N 56°04'39.16"

jbabco
Starting Member

8 Posts

Posted - 2007-12-04 : 17:01:30

Thanks for this, but still returning correct results. Results for ThreadsAnsweredByAdmin are always either 0 or 1, instead of the correct number of answered threads. I added these two records:


PostId  ForumId  ThreadId  PostDate   Username  UserRole
------  -------  --------  ---------  --------  --------
9       1        4         some date  mary      user
10      1        4         some date  ron       admin

jbabco
Starting Member

8 Posts

Posted - 2007-12-04 : 17:02:14

oops... should be "...but still NOT returning correct results..."

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-04 : 17:06:50

Oh come on!
Can't you add the new records in the same format as I posted earlier?
Are you so lazy you expect me to do all work for you?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-04 : 17:09:06

[code]DECLARE @Sample TABLE (PostId INT, ForumId INT, ThreadId INT, PostDate VARCHAR(9), Username VARCHAR(5), UserRole VARCHAR(5))

INSERT @Sample
SELECT 1, 1, 1, 'some date', 'bill', 'user' UNION ALL
SELECT 2, 1, 1, 'some date', 'james', 'admin' UNION ALL
SELECT 3, 1, 2, 'some date', 'mary', 'user' UNION ALL
SELECT 4, 2, 3, 'some date', 'steve', 'user' UNION ALL
SELECT 5, 2, 3, 'some date', 'ron', 'admin' UNION ALL
SELECT 6, 1, 1, 'some date', 'bill', 'user' UNION ALL
SELECT 7, 1, 1, 'some date', 'james', 'admin' UNION ALL
SELECT 8, 1, 1, 'some date', 'ron', 'admin' UNION ALL
SELECT 9, 1, 4, 'some date', 'mary' ,'user' UNION ALL
SELECT 10, 1, 4, 'some date', 'ron', 'admin'

SELECT ForumId,
COUNT(DISTINCT Admin) AS ThreadsAnsweredByAdmin,
COUNT(DISTINCT ThreadId) AS TotalThreads
FROM (
SELECT ForumId,
CASE
WHEN UserRole = 'admin' then ThreadId
ELSE NULL
END AS Admin,
ThreadId
FROM @Sample
) AS q
GROUP BY ForumId[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources