CODEWITHSUNDEEP
sql-serverms-access
Netanel Assor
Netanel Assor

Reputation: 1

Group BY inside a subquery

I am trying to select a count inside a subquery that is grouped by userid, unfortunatlly the userid has to be in the select part, and after doing so, the subquery returnes more then one value.

I Have two tables: Users that contains the column UserID and more columns, Messages that contains the column UserID (I am joining the two tables based on this column), MessageRead is a the field i want to count per user.

my query is this:

SELECT Users.UserID,  (SELECT COUNT(MessageRead) 
                       FROM Messages  
                       group by Messages.UserID) as d
from Messages 
right JOIN Users ON Users.UserID=Messages.UserID  group by Users.UserID;

thanks!

Upvotes: 0

Views: 61

Answers (1)

Matt Jenkins
Matt Jenkins

Reputation: 1383

I'm not sure what type of field MessageRead is. I'm guessing it's a boolean and you want to get a count of the messages that were read?

This query will get a count of all messages for a user.

SELECT u.UserID, COUNT(m.MessageID) FROM Users u LEFT JOIN Messages m on m.UserID = u.UserID GROUP BY u.UserID

If you want only the messages that are read, try adding a WHERE clause.

SELECT u.UserID, COUNT(m.MessageID) FROM Users u LEFT JOIN Messages m on m.UserID = u.UserID WHERE m.MessageRead = 1 GROUP BY u.UserID

The LEFT JOIN in this query will return users with 0 messages. If you want to exclude those then change it to an INNER JOIN.

Upvotes: 3

Related Questions