SQL Server: How to combine different Counts in one query

I would like to combine different Count statements in one query so that in the end the query just returns one number (total) for each Count.

The following queries work separately but I could not find a way to combine them without repeating the same result (nested tables) and without losing the separate names for each of them (Union All).

  • SQL order groups by group subtotal
  • Incorrect syntax near the keyword 'table' and could not extract ResultSet
  • SQL Server “<>” operator is very slow compared to “=” on table with a few million rows
  • how do i restart a table's primary key from 0 after deleting rows?
  • SQL query for distinct values
  • Using a temp table in a view
  • Can someone tell me how I can achieve this ?

    My SQL (just combined for easier view here):

    (
        SELECT      COUNT(*) OVER() AS countB
        FROM        MOC_Log2 B
        WHERE       B.modBy = @modBy
        AND         B.lastUpdate = 'Added'
        FOR XML PATH(''), ELEMENTS, TYPE
    ),
    (
        SELECT      COALESCE(SUM(D.vote), '0') AS countC
        FROM        MOC_Log3 C
        LEFT JOIN   MOC_Log3_Votes D
        ON          D.itemID = C.itemID
        WHERE       C.modBy = @modBy
        AND         C.lastUpdate = 'Added'
        FOR XML PATH(''), ELEMENTS, TYPE
    ),
    (
        SELECT      COALESCE(SUM(F.vote), '0') AS countE
        FROM        MOC_Log4 E
        LEFT JOIN   MOC_Log4_Votes F
        ON          F.itemID = E.itemID
        WHERE       E.modBy = @modBy
        AND         E.lastUpdate = 'Added'
        FOR XML PATH(''), ELEMENTS, TYPE
    )
    

    Many thanks for any help with this, Tim.

    One Solution collect form web for “SQL Server: How to combine different Counts in one query”

    just add null columns?

    SELECT      COUNT(*) OVER() AS countB,
                Null AS  countC,
                Null AS countE
    FROM        MOC_Log2 B
    WHERE       B.modBy = @modBy
    AND         B.lastUpdate = 'Added'
    FOR XML PATH(''), ELEMENTS, TYPE
    Union
    SELECT      null,
                COALESCE(SUM(D.vote), '0'),
                null
    FROM        MOC_Log3 C
    LEFT JOIN   MOC_Log3_Votes D
    ON          D.itemID = C.itemID
    WHERE       C.modBy = @modBy
    AND         C.lastUpdate = 'Added'
    FOR XML PATH(''), ELEMENTS, TYPE
    Union
    SELECT      null,
                null,
                COALESCE(SUM(F.vote), '0')
    FROM        MOC_Log4 E
    LEFT JOIN   MOC_Log4_Votes F
    ON          F.itemID = E.itemID
    WHERE       E.modBy = @modBy
    AND         E.lastUpdate = 'Added'
    FOR XML PATH(''), ELEMENTS, TYPE
    

    Also note I used Union over Union all. It is hard to tell if this would work for you as I cannot tell if the extra null columns may cause some error.

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