Monday, March 26, 2012
How to use two aggregate functions in RS 2005
table.
Basically it is (Sum of (Sum of X)).
How do we get around this?
Please let me know.
ThanksWhat might work for you is to add a calculated field. On the field list
where you drag and drop from just do a right mouse click, add a field (or
something like that). Then you can do a sum of that.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Anup" <anupkm@.gmail.com> wrote in message
news:1165428357.174972.171260@.l12g2000cwl.googlegroups.com...
>I am not able to use two aggregate functions to display a value in a
> table.
> Basically it is (Sum of (Sum of X)).
> How do we get around this?
> Please let me know.
> Thanks
>|||Hi Bruce,
Thank you very much for your reply.
So you mean to say that I need to add that field as a calculated field
instead of a normal field?
Thanks
Anup
Bruce L-C [MVP] wrote:
> What might work for you is to add a calculated field. On the field list
> where you drag and drop from just do a right mouse click, add a field (or
> something like that). Then you can do a sum of that.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Anup" <anupkm@.gmail.com> wrote in message
> news:1165428357.174972.171260@.l12g2000cwl.googlegroups.com...
> >I am not able to use two aggregate functions to display a value in a
> > table.
> > Basically it is (Sum of (Sum of X)).
> >
> > How do we get around this?
> > Please let me know.
> >
> > Thanks
> >|||If you add a calculated field to your dataset (this is from the list of
field, I am not talking about a sql statement here) you can have the
calculated field be an aggregate. Then you can aggregate the calculated
field, getting around your problem. I do this to make things simplier too.
Try it as I mentioned and see if it makes sense in your situation. Adding a
field manually to the field list returned by the query is not very
discoverable.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Anup" <anupkm@.gmail.com> wrote in message
news:1165432174.432662.162930@.f1g2000cwa.googlegroups.com...
> Hi Bruce,
> Thank you very much for your reply.
> So you mean to say that I need to add that field as a calculated field
> instead of a normal field?
> Thanks
> Anup
> Bruce L-C [MVP] wrote:
>> What might work for you is to add a calculated field. On the field list
>> where you drag and drop from just do a right mouse click, add a field (or
>> something like that). Then you can do a sum of that.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Anup" <anupkm@.gmail.com> wrote in message
>> news:1165428357.174972.171260@.l12g2000cwl.googlegroups.com...
>> >I am not able to use two aggregate functions to display a value in a
>> > table.
>> > Basically it is (Sum of (Sum of X)).
>> >
>> > How do we get around this?
>> > Please let me know.
>> >
>> > Thanks
>> >
>|||Thanks Bruce. Here is my situation:
I need to display the "Double Sum" Value in the footer of a table.The
details of this table is in a different level of grouping and footer is
at a different level of grouping.
Thanks
Anup
Bruce L-C [MVP] wrote:
> If you add a calculated field to your dataset (this is from the list of
> field, I am not talking about a sql statement here) you can have the
> calculated field be an aggregate. Then you can aggregate the calculated
> field, getting around your problem. I do this to make things simplier too.
> Try it as I mentioned and see if it makes sense in your situation. Adding a
> field manually to the field list returned by the query is not very
> discoverable.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Anup" <anupkm@.gmail.com> wrote in message
> news:1165432174.432662.162930@.f1g2000cwa.googlegroups.com...
> > Hi Bruce,
> >
> > Thank you very much for your reply.
> >
> > So you mean to say that I need to add that field as a calculated field
> > instead of a normal field?
> >
> > Thanks
> > Anup
> >
> > Bruce L-C [MVP] wrote:
> >> What might work for you is to add a calculated field. On the field list
> >> where you drag and drop from just do a right mouse click, add a field (or
> >> something like that). Then you can do a sum of that.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Anup" <anupkm@.gmail.com> wrote in message
> >> news:1165428357.174972.171260@.l12g2000cwl.googlegroups.com...
> >> >I am not able to use two aggregate functions to display a value in a
> >> > table.
> >> > Basically it is (Sum of (Sum of X)).
> >> >
> >> > How do we get around this?
> >> > Please let me know.
> >> >
> >> > Thanks
> >> >
> >
How to use the TOP(N) Filter syntax?
Could anyone provide information on how to use the "TOP(N)" filter syntax? I
want to return the top 10 aggregate rows. Not sure if it can do this?
Please help!
Thanks
TazYou mean like this:
SELECT TOP 500 Client.CLI_LastName
from Client
"Tarun Mistry" <nospam@.nospam.com> wrote in message
news:%235obeed0GHA.1268@.TK2MSFTNGP02.phx.gbl...
> Im tearing my hair out on this one.
> Could anyone provide information on how to use the "TOP(N)" filter syntax?
> I want to return the top 10 aggregate rows. Not sure if it can do this?
> Please help!
> Thanks
> Taz
>|||Just try something like this in your dataset
SELECT TOP (10) SomeField, COUNT(*) AS count
FROM SomeTable
GROUP BY SomeField
ORDER BY count DESC
That will count the number or rows pertaining to that specific field and
then put them in order by the count, showing only the top 10
"Tarun Mistry" <nospam@.nospam.com> wrote in message
news:%235obeed0GHA.1268@.TK2MSFTNGP02.phx.gbl...
> Im tearing my hair out on this one.
> Could anyone provide information on how to use the "TOP(N)" filter syntax?
> I want to return the top 10 aggregate rows. Not sure if it can do this?
> Please help!
> Thanks
> Taz
>|||Hi guys, thanks for the responses.
I cannot change my query, therefore the filtering has to happen after the
dataset has been returned. This is because below my chart I will sumamrise
ALL of the informarion, however I only want the chart to display the top 10
entires (or whatever).
I thought i could do something like..
Count(Field!MyAggregate.Value) Top N 10
However this didnt do anything (that I could see).
Kind Regards
Taz|||another option is two have 2 datasets, make them the exact same except add
the top N statement to the dataset related to the chart and then have your
second dataset without the top N for the summary information. You would
still get the same info but from 2 different datasets.
"Tarun Mistry" <nospam@.nospam.com> wrote in message
news:%23xy8bre0GHA.4648@.TK2MSFTNGP04.phx.gbl...
> Hi guys, thanks for the responses.
> I cannot change my query, therefore the filtering has to happen after the
> dataset has been returned. This is because below my chart I will
> sumamrise ALL of the informarion, however I only want the chart to display
> the top 10 entires (or whatever).
> I thought i could do something like..
> Count(Field!MyAggregate.Value) Top N 10
> However this didnt do anything (that I could see).
> Kind Regards
> Taz
>|||Many thanks for your help Ben,
your previous post was the one I needed to make me realise where I was going
wrong.
Thanks again,
Taz
"Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
news:OG$qsue0GHA.4044@.TK2MSFTNGP04.phx.gbl...
> another option is two have 2 datasets, make them the exact same except add
> the top N statement to the dataset related to the chart and then have your
> second dataset without the top N for the summary information. You would
> still get the same info but from 2 different datasets.
> "Tarun Mistry" <nospam@.nospam.com> wrote in message
> news:%23xy8bre0GHA.4648@.TK2MSFTNGP04.phx.gbl...
>> Hi guys, thanks for the responses.
>> I cannot change my query, therefore the filtering has to happen after the
>> dataset has been returned. This is because below my chart I will
>> sumamrise ALL of the informarion, however I only want the chart to
>> display the top 10 entires (or whatever).
>> I thought i could do something like..
>> Count(Field!MyAggregate.Value) Top N 10
>> However this didnt do anything (that I could see).
>> Kind Regards
>> Taz
>
Friday, March 23, 2012
How to use sum and count aggregate functions with xml data type in SQL Server 2005
Hi:
I have xml in sql server 2005 beta2 that is stored as an xml data type and I'm trying to run some aggregate functions like count and sum, but I don't know how to do it. The elements are numeric values, but 'sum' and also 'count' return errors. I have included the error message and the sp below. Any ideas how to use sum and count correctly?
Thanks,
Paul
Msg 8117, Level 16, State 1, Procedure get_vID_FormXML_summary, Line 11
Operand data type varchar is invalid for sum aggregate operator.
--
alter procedure dbo.get_vID_FormXML_summary
@.ID int
as
SELECT FormXML.query(sum('/transactionAmounts/transactionShares/value'))
AS Result
FROM _Company_Info_01_FLAT
WHERE (ID = @.ID)
--GROUP BY DateFiled, FormXML
This looks like a typing issue. Is the XML datatype typed with a schema or untyped? You may also upgrade to RTM (or at least the Sept CTP). The behaviour and error messages have been vastly improved since Beta2. Best regards Michael <kloepper@.discussions.microsoft.com> wrote in message news:032b5300-6b08-4886-8440-fcf708316b00_WBRev1_@.discussions..microsoft.com...This post has been edited either by the author or a moderator in the Microsoft Forums: http://forums.microsoft.com Hi:I have xml in sql server 2005 beta2 that is stored as an xml data type and I'm trying to run some aggregate functions like count and sum, but I don't know how to do it. The elements are numeric values, but 'sum' and also 'count' return errors. I have included the error message and the sp below. Any ideas how to use sum and count correctly?Thanks,PaulMsg 8117, Level 16, State 1, Procedure get_vID_FormXML_summary, Line 11 Operand data type varchar is invalid for sum aggregate operator. --alter procedure dbo.get_vID_FormXML_summary @.ID int as SELECT FormXML.query(sum('/transactionAmounts/transactionShares/value')) AS Result FROM _Company_Info_01_FLAT WHERE (ID = @.ID) --GROUP BY DateFiled, FormXML|||I checked the version again, it is 9.00.1116.00, that might be the Sept CTP.Anyway, the xml has no schema associted with it, except the dbo schema.
So, what do you suggest now?
Thanks,
Paul|||
Hi.
Subquery or User defined aggregate.
The subquery is easiest:
-- Snippet
USE adventureworks
GO
WITH SubQ AS (
SELECT LineTotal AS Num FROM Sales.SalesOrderDetail
)
Select Sum(Num) FROM SubQ
-- EoSnippet
Of course, LineTotal should be replaced with some XML stuff and a conversion...
Hope this helps
|||Actually, it looks like the 'conversion' (I assume that you mean the type casting error) is what I need help with.I get the following error using the subquery method...this looks like the error that I got with the first method i tried previously.
So how do I cast this properly? Any literature on the subject?
Thanks,
Paul
USE SEC
GO
WITH SubQ
AS
(SELECT FormXML
AS '/transactionAmounts/transactionShares/value'
FROM _Company_Info_01_FLAT)
SELECT SUM('/transactionAmounts/transactionShares/value')
AS
SumOfValues
FROM SubQ
AS
SubQ_1
Operand data type varchar is invalid for sum aggregate operator.
In your case, you should rewrite the query to be:
SELECT SUM(FormXML.value('(/transactionAmounts/transactionShares/value/text())[1]', 'int'))
AS Result
If you have multiple <value /> tags within a single instance that you are trying to get the sum of, then you can use the XQuery sum() function:
SELECT SUM(FormXML.value('sum(/transactionAmounts/transactionShares/value/text())', 'int'))
AS Result
In the second case, note that the "sum" is within the string literal that is our XQuery expression, because we are invoking the XQuery sum aggregate.
|||You have two ways to do sums: You can use the XPath/XQuery sum() function inside an XQuery expression that you call through either the query() or as John mentions through the value() method (if you want to cast the value to a SQL value). Also, it depends on whether you want to do the sum over all elements or only all elements within a row.
In your example, you are mixing the metaphors.
And build 9.00.1116.00 is much older than the September CTP (probably the December 04 CTP).
Try the following:
create table SumT(x xml)
go
insert into SumT
select N'<a><b>1</b><b>2</b></a>'
union
select N'<a><b>3</b><b>4</b></a>'
go
select SUM(B.n.value('.', 'decimal'))
from SumT cross apply x.nodes('/a/b') B(n)
select SUM(x.value('sum(/a/b)', 'decimal'))
from SumT
select x.value('sum(/a/b)', 'decimal')
from SumT
Best regards
Michael
|||I want the sum of multiple rows, so this one below works for me.select SUM(B.n.value('.', 'decimal'))
from SumT cross apply x.nodes('/a/b') B(n)
It does, however, give slightly different results than the first method that John demonstrated. Your second and third method, as well as John's second method return casting errors.
I feel as though I'm looking for a needle in a haystack in trying to find answers looking through the MSDN documentation. Is that the best place, or can you suggest another source or good book that includes the subject of writing xml queries in SQL Server 2005. Perhaps I just need to take a closer look at the MSDN explanations?
Thanks for your help,
Paul
|||John:
Thanks for the help. I'm beginning to understand this a little better, but it's going to take some more work.
I'm just wondering if you have any favorite books or web sites to learn xqueries for SQL Server 2005? I've just been using MSDN2, but it seems to be a struggle to find what I'm looking for.
Paul|||Dear Paul
First, both John's and my queries should work. E.g., the expression I posted I copied from my management studio window after I made sure that they would give me results. If you see errors just running the queries above, I strongly recommend that you upgrade to the September CTP or the RTM version.
Regarding books, and other resources: I recommend our team's weblogs (you can start with mine at http://www.sqljunkies.com/weblog and follow the links to others).
We also have an XQuery whitepaper on MSDN.microsoft.com (linked from me weblog) and the latest versions of Book Online also have quite a bit about the XQuery support.
I hope this helps
Michael