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

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:

  • SQL Server/Dynamics CRM: How to read document body from Annotation Base using SQL only
  • Update table inserting VARBINARY data
  • TransactionScope TransactionAborted Exception - transaction not rolled back. Should it be?
  • T-SQL - GROUP BY with LIKE - is this possible?
  • migrating carriage returns from oracle to sql server
  • GET Month, Quarter based on Work Week number
  • 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();
    

  • SQL Server Replication - Auto resolve conflicts
  • sql server creation of logins and users by another user
  • Create a table user defined function in my DB from C#
  • How to Connect to SQL Server using LINQ to SQL?
  • How do you get full error information for a stored procedure error out of LINQ-to-SQL or SQL Server?
  • How do I increase the Command Timeout in OrmLite ServiceStack?
  • 5 Solutions collect form web for “How do I count two entries in one SQL Query in C#?”

    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
    

    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
    

    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.

    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.

    Just do

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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.