Cannot COUNT records while using CTE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Cannot COUNT records while using CTE

Author

Topic

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2013-09-03 : 15:51:45

I'm utilizing a CTE which is working perfectly, except I cannot seem to count the number of records.

Here is the current query:

quote:

;with cte_assets as (
   select a.f_locationid, a.f_locationparent, a.f_locationname, 0 as [lev], convert(varchar(30), '0_' + convert(varchar(10), f_locationid)) lineage
   from tb_locations a where f_locationID = '290' UNION ALL
   select a.f_locationid
          ,a.f_locationparent
          ,a.f_locationname
          ,c.[lev] + 1
          ,convert(varchar(30), lineage + '_' + convert(varchar(10), a.f_locationid))
   from   cte_assets c
   join   tb_locations a
          on a.f_locationparent = c.f_locationID
)
 select f_assettag, f_assetID, f_locationname from cte_assets c
 JOIN tb_assets ass on ass.f_assetlocation = c.f_locationID

However, if I try to add a COUNT(*) to the final select, I get an error:

Updated query with COUNT(*):

quote:

 ;with cte_assets as (
   select a.f_locationid, a.f_locationparent, a.f_locationname, 0 as [lev], convert(varchar(30), '0_' + convert(varchar(10), f_locationid)) lineage
   from tb_locations a where f_locationID = '290' UNION ALL
   select a.f_locationid
          ,a.f_locationparent
          ,a.f_locationname
          ,c.[lev] + 1
          ,convert(varchar(30), lineage + '_' + convert(varchar(10), a.f_locationid))
   from   cte_assets c
   join   tb_locations a
          on a.f_locationparent = c.f_locationID
)
 select f_assettag, f_assetID, f_locationname, COUNT(f_locationname) as f_totalrecords 
 from cte_assets c
 JOIN tb_assets ass on ass.f_assetlocation = c.f_locationID

The error with the second query is as follows:

quote:

Msg 8120, Level 16, State 1, Line 13
Column 'tb_assets.f_assettag' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Any help is appreciated.
Thanks in advance,
Matt

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2013-09-03 : 15:57:22

Sorry, I should have searched the forums first. I found the answer after I posted: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=152550 I needed to use "COUNT(*) OVER ()" to accomplish what I needed. This works now as

quote:

;with cte_assets as (
   select a.f_locationid, a.f_locationparent, a.f_locationname, 0 as [lev], convert(varchar(30), '0_' + convert(varchar(10), f_locationid)) lineage
   from tb_locations a where f_locationID = '290' UNION ALL
   select a.f_locationid
          ,a.f_locationparent
          ,a.f_locationname
          ,c.[lev] + 1
          ,convert(varchar(30), lineage + '_' + convert(varchar(10), a.f_locationid))
   from   cte_assets c
   join   tb_locations a
          on a.f_locationparent = c.f_locationID
)
 select f_assettag, f_assetID, f_locationname, COUNT(*) OVER ()f_totalrecords
 from cte_assets c
 JOIN tb_assets ass on ass.f_assetlocation = c.f_locationID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources