SQL Server count(*) issue

Question

Hello everyone,

I am using SQL Server 2008 Enterprise. I am using the following statement in SQL Server Management Studio as a part of a store procedure, and there is following error (compile error when I press F5 to run the store procedure). But when I removed count(), all error disappears. Any ideas what is wrong with count()? Another question is, my purpose is to return the total number of matched result and only return a part of result to implement paging (using tt.rowNum between @startPos and @requireCount + @startPos-1), any ideas how to implement that?

SELECT * 
    FROM   (SELECT  count(*), t.id,t.AdditionalInfo, ROW_NUMBER() 
       OVER (order by t.id) AS rowNum
FROM dbo.foo  t 
    CROSS APPLY t.AdditionalInfo.nodes('/AdditionalInfo') 
          AS MyTestXMLQuery(AdditionalInfo) 
WHERE 
    (Tag4=''+@InputTag4+'' OR Tag5=''+@InputTag5+'') 
    and (MyTestXMLQuery.AdditionalInfo.value
              ('(Item[@Name="Tag1"]/@Value)[1]', 'varchar(50)') 
          LIKE '%'+@Query+'%' 
    or MyTestXMLQuery.AdditionalInfo.value
              ('(Item[@Name="Tag2"]/@Value)[1]', 'varchar(50)') 
          LIKE '%'+@Query+'%' 
    or MyTestXMLQuery.AdditionalInfo.value
              ('(Item[@Name="Tag3"]/@Value)[1]', 'varchar(50)') 
          LIKE '%'+@Query+'%') ) tt 
    WHERE  tt.rowNum between @startPos and  @requireCount + @startPos-1

Error message,

Column 'dbo.foo.ID' is invalid in the select list 
      because it is not contained in either an aggregate function 
      or the GROUP BY clause.
No column name was specified for column 1 of 'tt'.

thanks in advance, George

Answer 1

Replace it with

SELECT count(*) over() AS [Count]

It needs an alias as it is a column in a derived table.

The empty over() clause will return the count in the whole derived table. Is that what you need?

Answer 2

You generally can't mix aggregate functions and normal field selections without a GROUP BY clause.

In queries where you are only selecting a COUNT(*) it assumes you mean to lump everything together in one group. Once you select another field (without a corresponding GROUP BY), you introduce a contradiction to that assumption and it will not execute.

Answer 3

You need to have a GROUP BY clause. Try this:

SELECT * 
 FROM   (SELECT 
    count(*) AS c, t.id,t.AdditionalInfo
   FROM 
    dbo.foo  t 
   CROSS APPLY 
    t.AdditionalInfo.nodes('/AdditionalInfo') AS MyTestXMLQuery(AdditionalInfo) 
   WHERE 
    (Tag4=''+@InputTag4+'' OR Tag5=''+@InputTag5+'') 
    and (MyTestXMLQuery.AdditionalInfo.value('(Item[@Name="Tag1"]/@Value)[1]', 'varchar(50)') LIKE '%'+@Query+'%' 
    or MyTestXMLQuery.AdditionalInfo.value('(Item[@Name="Tag2"]/@Value)[1]', 'varchar(50)') LIKE '%'+@Query+'%' 
    or MyTestXMLQuery.AdditionalInfo.value('(Item[@Name="Tag3"]/@Value)[1]', 'varchar(50)') LIKE '%'+@Query+'%')
    GROUP BY t.id,t.AdditionalInfo
    ) tt 
    WHERE  tt.rowNum between @startPos and  @requireCount + @startPos-1

There might be more. Not sure.

Either way, it would do you a lot of good to learn about the theory behind the relational database model. This query needs a lot more help than what I just added. I mean it needs A LOT more help.

Edit: You also can't have a ROW_NUMBER() in a query that selects COUNT(*). What would you be trying to number? The number of Counts?

Answer 4

A guess, cause I can't run it, but try Changing it to:

Select * From  
   (Select count(*), t.id, t.AdditionalInfo, ROW_NUMBER()  
        OVER (order by t.id) AS rowNum 
    From dbo.foo  t  
       CROSS APPLY t.AdditionalInfo.nodes('/AdditionalInfo')  
          AS MyTestXMLQuery(AdditionalInfo)  
    Where 
        (Tag4=''+@InputTag4+'' OR Tag5=''+@InputTag5+'')  
        and (MyTestXMLQuery.AdditionalInfo.value 
              ('(Item[@Name="Tag1"]/@Value)[1]', 'varchar(50)')  
          LIKE '%'+@Query+'%'  
       or MyTestXMLQuery.AdditionalInfo.value 
              ('(Item[@Name="Tag2"]/@Value)[1]', 'varchar(50)')  
          LIKE '%'+@Query+'%'  
       or MyTestXMLQuery.AdditionalInfo.value 
              ('(Item[@Name="Tag3"]/@Value)[1]', 'varchar(50)')  
          LIKE '%'+@Query+'%')
     Group By t.id, t.AdditionalInfo ) tt  
Where tt.rowNum between @startPos and  @requireCount + @startPos-1