Sum Aliases (not explicit fields) 1

[techkate]

I'm looking to get the sum of two counts into one value/attribute in the XML returned.

Here is the simplified query as it returns now, with two different sum columns:

select 1 as TAG,
null as PARENT,
'No Status Set' as [XMLTest!1!Status],
count(*) as [XMLTest!1!mainCount],
(select count(*) from Table2 aa) as [XMLTest!1!subCount]
from Table1 a
for xml explicit

This returns:

<XMLTest Status="No Status Set" mainCount="691" subCount="300"/>


I'm looking for a way to return just one count representing the sum of mainCount and subCount, with the resulting XML looking like this:

<XMLTest Status="No Status Set" totalCount="991"/>

I have tried adding a sum into the select statement which would combine mainCount and subCount. This is not working, because SQL seems to need an explicit field name, rather than an alias.

Does anyone have any suggestions to help me out?

Thanks,


Kate

[small]Yeah, it's a non-nutritive cereal varnish. It's semi-permeable. It's not osmotic.[/small]
[small]What it does is it coats and seals the flake, prevents the milk from penetrating it."[/small]

 

[bborissov]

select 1 as TAG,
null as PARENT,
'No Status Set' as [XMLTest!1!Status],
count(*) as [XMLTest!1!mainCount],
(select count(*) from Table2 aa)+COUNT(*) as [XMLTest!1!subCount]
from Table1 a
for xml explicit

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[techkate]

Thanks so much, bborissov. This works beautifully.

However, I really like to fully understand any answers I receive in this forum.

Could you explain the reasons why the query has to be performed in this way?

Thanks again!

Kate

Kate

[small]Yeah, it's a non-nutritive cereal varnish. It's semi-permeable. It's not osmotic.[/small]
[small]What it does is it coats and seals the flake, prevents the milk from penetrating it."[/small]

 

[bborissov]

WOW! What an ugly answer I post :-( Sorry for that. I forgot to close code tag. So The code must look like this:

[code]
select 1               as TAG,
       null            as PARENT,
       'No Status Set' as [XMLTest!1!Status],
       (select count(*) from Table2 aa) +
       COUNT(*)        as [XMLTest!1!totalCount]
from Table1 a
for xml explicit

As you see the final field is sum of both counts, this from Table2 and this from Table1. I realy don't know what to explain With (select count(*) from Table2 aa) you get a count from Table2 and you add COUNT(*) from Table1 to it, the result field is total from both counts.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP