Conditionnally count a number of rows

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)

Conditionnally count a number of rows

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-04-01 : 08:47:33

writes "Hi,

I am working with 3 tables.
The first one contains data on many users, it is called Users... . It contains a field called userid, the index.
The second one Messages (contains lots of entries of messages written by different users coming from the users tables). Contains an index which is the messageid, and contains a field called author corresponding to one of the users in the Users table.
The final one Ratings contains ratings given to messages (like acceptance rating and so on) by other users.
I would like to get a query to return a "summary" containing every user, the number of messages written by this user in the table, and the number of messages of this user having a rating higher than 5. I mean something like this:


_______________________________________________________
User         Name         Messages        GoodMessages
1         "Sami Dee"          42               26
2         "Bibi"              154              13
3         "Kerri Chandler"    220              211
4         "Greg Gauthier"     87               81
5         "Sven Love"         213              149
6         ...
7         ...
8         ...
_______________________________________________________

My initial query does not display "GoodMessages" but looks like this:

SELECT Users.Userid, Users.Username, COUNT(Messages.Messageid) As Messages
FROM Users LEFT JOIN Messages ON Messages.Author = Users.Userid LEFT JOIN Ratings ON Messages.Messageid = Ratings.Messageid
WHERE Messages.TimeStamp BETWEEN '1 january 2002' AND '30 march 2004'
GROUP BY Users.Userid, Users.Username
ORDER BY Users.Userid

Could you help me to get the code to get the last column?

Thx"

nr
SQLTeam MVY

12543 Posts

Posted - 2004-04-01 : 09:06:18

GoodMessages = sum(case when rating > 5 then 1 else 0 end)

==========================================
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.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-04-01 : 09:07:34

You could use a CASE expression to calculate the last column:


SELECT Users.Userid, Users.Username, COUNT(Messages.Messageid) As Messages,
SUM(CASE WHEN Ratings.Rating > 5 THEN 1 ELSE 0 END) AS GoodMessages
FROM Users LEFT JOIN Messages ON Messages.Author = Users.Userid LEFT JOIN Ratings ON Messages.Messageid = Ratings.Messageid
WHERE Messages.TimeStamp BETWEEN '1 january 2002' AND '30 march 2004'
GROUP BY Users.Userid, Users.Username
ORDER BY Users.Userid

Of course assuming here that you have a column in the Ratings tables called "Rating" (what else?!) that stores the actual rating of the message.

EDIT: Damn

!
OS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources