How do I count two entries in one SQL Query in C#?

Question

I have two columns in my ForumPost Table: Id and ThreadId.

I want to count all Entries in Id and ThreadId for a specific User:

Code snippet

cmd = new SqlCommand();
cmd.Connection = connection;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = @"SELECT dbo.ForumPost.ThreadId, "
                    + "Id, "
                    + " COUNT(ForumPost.Id)AS PostCount AND (ForumPost.ThreadId)AS
                        ThreadCount"
                    + " FROM ForumPost "
                    + " WHERE UserName = @UserName ";
cmd.Parameters.Add(new SqlParameter("@UserName", ThreadUserName));
reader = cmd.ExecuteReader();

if (reader.HasRows)
{
    while (reader.Read())
    {
        int postCount = Convert.ToInt16(reader["PostCount"]);
        int threadCount = Convert.ToInt16(reader["ThreadCount"]);
        AllPosts = postCount + threadCount;
    }
}
reader.Close();

Answer 1

If a User can have different ThreadId count and Id count, you'll have to split that up into separate queries.

SELECT COUNT(Id) as PostCount, UserName
WHERE UserName = '@UserName'
GROUP BY Id, UserName


SELECT COUNT(ThreadId) as ThreadCount, UserName
WHERE UserName = '@UserName'
GROUP BY Id, UserName

Answer 2

Try

SELECT COUNT(ForumPost.Id)AS PostCount AND (ForumPost.ThreadId)AS ThreadCount" 
                                    // + " COUNT(ForumPost.ThreadId)AS ThreadCount " 
                                     + " FROM ForumPost " 
                                     + " WHERE UserName = @UserName ";

Your selecting of ID and ThreadID is causing a record to be returned for each which is why you're getting a double count.

Answer 3

Just do

SELECT COUNT(*) FROM ForumPost WHERE UserName = @UserName;

Answer 4

I'm guessing a bit here, but I think you want a count of the number of posts the user has made and the number of different (distinct) threads that the user has participated in:

SELECT 
    (
        SELECT COUNT(Id)
        FROM ForumPost
        WHERE UserName = @UserName
    ) AS PostCount,
    (
        SELECT COUNT(DISTINCT ThreadId)
        FROM ForumPost
        WHERE UserName = @UserName
    ) AS ThreadCount

Answer 5

When you use COUNT(Column) is going to count +1 for each row that has a non-null value in the Column. So if you SELECT COUNT(Id) as PostCount, COUNT(ThreadId) as ThreadCount you are likely going to get the same numbers in both counts, since you count how many rows have non-null Id and ThreadId respectively.

What is likely your intent is that you want to count how many posts in how many individual threads has the user posted, for this you can use COUNT(DISTINCT Column):

cmd.CommandText = @"
   SELECT  COUNT(ForumPost.Id) AS PostCount 
      , COUNT(DISTINCT ForumPost.ThreadId) AS ThreadCount
   FROM ForumPost 
   WHERE UserName = @UserName ";

However, it is not clear what is your actual intent, since you're mixing this with retrieving the Id and the ThreadId too. You could mean that you want the total count of posts in each thread, the count of posts by this individual user in any thread he posted, or you want a list of posts and a total count (ie. two separate queries). You'll need to explain.