TSQL getting record count and records in single query

Question

I got this tasks table that has TODO items. We are retrieving the todo items and the count of Finished, Pending tasks using separate query in single stored procedure even though it is querying from same table. Here is the query,

select
TaskName 'Task/TaskName',
CASE IsDone WHEN '1' THEN 'True' ELSE 'False' END 'Task/IsDone',
(
 SELECT COUNT(*) FROM Tasks WHERE IsDone = '1'
) 'CompletedCount'
FROM Tasks FOR XML PATH('Tasks')

here is the output

'<Tasks>
    <Task>
        <TaskName>Write a email to Mayor<TaskName>
        <IsDone>True</IsDone>
        <CompletedCount>2<CompletedCount>
    </Task>
</Tasks>'

CompletedCount is present in each Task which is unnecessary also is there anyway i can query the count too without explicitly writing this SELECT COUNT(*) FROM Tasks WHERE IsDone = '1'

How do i get a output as below

'<Tasks>
    <CompletedCount>2<CompletedCount>
    <Task>
        <TaskName>Write a email to Mayor<TaskName>
        <IsDone>True</IsDone>
    </Task>
    <Task>
        <TaskName>Organize Campaign for website<TaskName>
        <IsDone>False</IsDone>
    </Task>
</Tasks>'

Mikael Eriksson · Accepted Answer · 2012-01-09T07:50:12.843

select (
          select count(*)
          from Tasks
          where IsDone = 1
          for xml path('CompletedCount'), type
       ),
       (
          select TaskName,
                 case IsDone 
                   when 1 then 'True' 
                   else 'False' 
                 end as IsDone
          from Tasks
          for xml path('Task'), type
       )
for xml path('Tasks')

Update:
You can do it with a singe select if you first build your task list and then query the XML for the completed count. I doubt this will be any faster than using two select statements.

;with C(Tasks) as
(
  select TaskName,
         case IsDone 
           when 1 then 'True' 
           else 'False' 
         end as IsDone
  from Tasks
  for xml path('Task'), type
)
select C.Tasks.value('count(/Task[IsDone = "True"])', 'int') as CompletedCount,
       C.Tasks
from C
for xml path('Tasks')

again it's mikael How do you do? btw i do know how to structure the xml but how do i do it in single select statement rather than `select for count`, `select for columns` i think it's not possible when having aggregate functions — Deeptechtons, Jan 09 '12 at 07:17
@Deeptechtons - Updated answer but I don't think it will perform any better. But you will never know unless you try it :). — Mikael Eriksson, Jan 09 '12 at 07:45

score 2 · Answer 2 · answered Jan 09 '12 at 07:00

You can use type to calculate part of the XML in a subquery:

declare @todo table (TaskName varchar(50), IsDone bit)
insert @todo values ('Buy milk',1)
insert @todo values ('Send thank you note',1)

select  sum(case when isdone = 1 then 1 end) as 'CompletedCount'
,       (
        select  TaskName 'TaskName'
        ,       case when isdone = 1 then 'True' else 'False' end 'IsDone'
        from    @todo
        for xml path('Task'), type
        ) as 'TaskList'
from    @todo
for xml path('Tasks')

This prints:

<Tasks>
    <CompletedCount>2</CompletedCount>
    <TaskList>
        <Task>
            <TaskName>Buy milk</TaskName>
            <IsDone>True</IsDone>
        </Task>
        <Task>
            <TaskName>Send thank you note</TaskName>
            <IsDone>True</IsDone>
        </Task>
    </TaskList>
</Tasks>

TSQL getting record count and records in single query

How do i get a output as below

2 Answers2