COUNT without GROUP BY clause issue

Question

I'm probably missing something simple here. I have this first table:

CREATE TABLE [Orgnzs] (
    [id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    [nm] NVARCHAR(256)
);

and then also several tables that are all created as such (all having the same structure):

-- WLog_* tables are all created as such
CREATE TABLE [WLog_1] (
    [id] BIGINT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    [huid] UNIQUEIDENTIFIER,
    [dtin] BIGINT,
    [dtout] BIGINT,
    [cnm] NVARCHAR(15),
    [batt] TINYINT,
    [pwrop] TINYINT,
    [pst] INT,
    [flgs] INT,
    [ppocs] NVARCHAR(1024),
    [ppocu] NVARCHAR(1024),
    [por] NVARCHAR(1024)
);

and a similar set of tables, without last 3 columns of the table above:

-- ULog_* tables are all created as such
CREATE TABLE [ULog_1] (
    [id] BIGINT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    [huid] UNIQUEIDENTIFIER,
    [dtin] BIGINT,
    [dtout] BIGINT,
    [cnm] NVARCHAR(15),
    [batt] TINYINT,
    [pwrop] TINYINT,
    [pst] INT,
    [flgs] INT
);

My goal is to select records from arbitrary set of WLog_* and ULog_* tables, and limit it by manageable number of elements (for page layout) for which I also need to know the total count of records found.

So I do selection as such:

SELECT  b.[id] AS evtID,
        b.[huid] as huid,
        b.[dtin] as dtin,
        b.[dtout] as dtout,
        b.[cnm] as cnm,
        b.[batt] as batt,
        b.[pwrop] as pwrop,
        b.[pst] as pst,
        b.[flgs] as flgs,
        b.[ppocs] as ppocs,
        b.[ppocu] as ppocu,
        b.[por] as por,
        b.[orgID] as orgID,
        b.[wLg] as wLg,
        orgz.[nm] as orgNm
        , COUNT_BIG(*) as allRecordsFound
 FROM (
    -- next also specify the column(s) to sort by
    SELECT *, ROW_NUMBER() OVER (ORDER BY [dtin], [cnm] ASC) AS rw FROM (
        SELECT *, 1 AS orgID, 1 AS wLg
          FROM [WLog_1]
        UNION ALL
        SELECT *, 2 AS orgID, 1 AS wLg
          FROM [WLog_2]
        UNION ALL
        SELECT *, NULL AS [ppocs], NULL AS [ppocu], NULL AS [por], 1 AS orgID, 0 AS wLg
          FROM [ULog_1]
    ) a
    WHERE [pst]&1=1 OR [pst]=67
) b 
LEFT JOIN [Orgnzs] AS orgz ON orgID=orgz.[id]
WHERE rw >= 2 AND rw <= 4       -- restrict for a page only

which unfortunately fails on the COUNT_BIG(*) as allRecordsFound line with the following error:

Column 'b.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I haven't used SQL Server for a while, can someone suggest what am I missing here?

PS. For a test purpose I made a Fiddle to try it out.

Answer 1

Use this instead:

count(*) over() as allRecordsFound

You can mix window aggregation function in select statement whithout grouping.