sum of the values xml - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

sum of the values xml

Author

Topic

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2011-10-24 : 15:07:18

I'm trying to get the sum of the values from control, prod, beta. see result below. Much appreaciated

RESULT:
---------------------------------------------------------------------------
[name] [beta][control] [Production] [total traffic]
---------------------------------------------------------------------------
ga:visits 10 0 0 10
ga:visitors 5 20 25 50
---------------------------------------------------------------------------

declare @table table (x xml)
insert @table
select '<KPICounts>
<KPICount SiteID="2" TestID="1" TestGroupID="1" GroupName="Production" Name="ga:visitors" DataType="integer" Value="20" />
<KPICount SiteID="2" TestID="1" TestGroupID="2" GroupName="Beta" Name="ga:visitors" DataType="integer" Value="5" />
<KPICount SiteID="2" TestID="1" TestGroupID="3" GroupName="Control" Name="ga:visitors" DataType="integer" Value="10" />
</KPICounts>'
union all
select '<KPICounts>
<KPICount SiteID="2" TestID="1" TestGroupID="1" GroupName="Production" Name="ga:visitors" DataType="integer" Value="5" />
<KPICount SiteID="2" TestID="1" TestGroupID="2" GroupName="Beta" Name="ga:visits" DataType="integer" Value="10" />
<KPICount SiteID="2" TestID="1" TestGroupID="3" GroupName="Control" Name="ga:visitors" DataType="integer" Value="10" />
</KPICounts>'

select x.value('(/KPICounts/KPICount/@Name)[1]', 'varchar(30)') from @table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-25 : 00:27:18

still you dont know logic to be applied for this? I have given you similar solutions atleast twice before this.


declare @table table (x xml)
insert @table
select '<KPICounts>
<KPICount SiteID="2" TestID="1" TestGroupID="1" GroupName="Production" Name="ga:visitors" DataType="integer" Value="20" />
<KPICount SiteID="2" TestID="1" TestGroupID="2" GroupName="Beta" Name="ga:visitors" DataType="integer" Value="5" />
<KPICount SiteID="2" TestID="1" TestGroupID="3" GroupName="Control" Name="ga:visitors" DataType="integer" Value="10" />
</KPICounts>'
union all
select '<KPICounts>
<KPICount SiteID="2" TestID="1" TestGroupID="1" GroupName="Production" Name="ga:visitors" DataType="integer" Value="5" />
<KPICount SiteID="2" TestID="1" TestGroupID="2" GroupName="Beta" Name="ga:visits" DataType="integer" Value="10" />
<KPICount SiteID="2" TestID="1" TestGroupID="3" GroupName="Control" Name="ga:visitors" DataType="integer" Value="10" />
</KPICounts>'

select Name,
coalesce([Beta],0) AS [Beta],
coalesce([Control],0) AS [Control],
coalesce([Production],0) AS [Production],
coalesce([Beta],0) +coalesce([Control],0)+coalesce([Production],0) AS [Total]
from
(
select m.n.value('(./@Name)[1]', 'varchar(30)') as Name,
m.n.value('(./@GroupName)[1]', 'varchar(30)') as GroupName,
m.n.value('(./@Value)[1]', 'int') AS Value
from @table t
cross apply t.x.nodes('/KPICounts/KPICount')m(n)
)t
pivot (sum(Value) for GroupName IN ([Beta],[Control],[Production]))p

output
-----------------------------------------
Name	Beta	Control	Production	Total
ga:visitors	5	20	25	50
ga:visits	10	0	0	10

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2011-10-25 : 03:47:08

I went home and wrote something similar...

Thanks V.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-25 : 04:09:43

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources