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:

  • Does SQL Profiler work with LocalDB?
  • Failed to CREATE AN ASSEMBLY in SQL
  • Query optimisation
  • Querying different table based on a parameter
  • How to split a comma-separated value to columns
  • Compare two DATETIME only by date not time in SQL Server 2008
  • 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();
    

  • How do I fix an error connecting to SQL Server: SSL Provider, error: 0 - The message received was unexpected or badly formatted
  • Create the Provider db tables after RecreateDatabaseIfModelChanges
  • Crystal Report throws 'Failed to open the connection.' only on postback
  • How to make SqlContext.Pipe.Send in SQLCLR stored proc work with unicode?
  • Release Management - releasing to a subset of users - how would it work for a public facing website
  • What's a good alternative to firing a stored procedure 368 times to update the database?
  • 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.