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.

score 3 · Accepted Answer · answered Oct 13 '15 at 05:03

3

Use this instead:

count(*) over() as allRecordsFound

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

answered Oct 13 '15 at 05:03

Giorgi Nakeuri

35,155
8
47
75

Thanks. It solves the error, but it returns the count of rows returned according to `WHERE rw >= 2 AND rw <= 4` condition and not the full count according to `[pst]&1=1 OR [pst]=67` condition. Am I missing one more nested SELECT or a view there? – c00000fd Oct 13 '15 at 05:08
Oh, got it. Needed to put the count inside my `a` SELECT... thanks. – c00000fd Oct 13 '15 at 05:12
Your fiddle returns 3 rows. And it shows 3 as allRecordsFound. Whar else are you expecting? – Giorgi Nakeuri Oct 13 '15 at 05:12
The correct result was supposed to be 6, like in `N out of 6` records for a page layout. But I got it fixed, like I posted above. – c00000fd Oct 13 '15 at 05:14
One follow-up question. Can you explain what this `over()` addition does? Also does it add any significant performance hit to this query? – c00000fd Oct 13 '15 at 05:16
See this answer for details http://stackoverflow.com/questions/14860162/tsql-over-clause-count-over-order-by-a – Giorgi Nakeuri Oct 13 '15 at 05:20

COUNT without GROUP BY clause issue

1 Answers1