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 sum to add specific data from the table in ssrs 2000?
Hi All,
I am new to this forum and had checked this forum for this problem that I am having. Although there were a lot of informative articles that I found useful, I still couldn't resolve this problem.
I am having a little trouble trying to use the Sum function to get the totals that I need for a project that I am trying to accomplish. The scenario is below:-
This is the report image that I want to produce.
Region1 policy1 policy2
AgentType1 Total Total
Agenttype2 Total Total
AgentType3 Total Total
Region1's policy1 Total Region1 Policy2 Total
Region2 policy1 policy2
AgentType1 Total Total
Agenttype2 Total Total
AgentType3 Total Total
Region2's policy1 Total Region2 Policy2 Total
(this is where i am having the problem)
AgentTotals
AllAgentType1Totals Policy1Totals Policy2Totals
AllAgentType2Totals Policy1Totals Policy2Totals
TotalforallRegions RegionsTotals
I managed to get all the information I needed in the
Report except for Agent Totals. I am having a little
bit of a logic problem on how to get the totals for a
particular agent type with each policy.
Right now my layout only has ONE grouping which holds
the region types. my dataset basically is how the
report is going to look like without the totals.
Is there an expression I can use that would identify
what type of agent that is and then add the total ?
kinda like the following:-
=IIF(agenttype = "AgentType1", Sum(all agent totals
for policy 1), "")
Am I heading in the right direction ?
Any tips or hints would be greatly appreciated.
Sincerely,
Bernard Ong
After much searching and deciphering, I finally figure out how to do this.
The expression I had used was slightly wrong, though it was in the right direction.
The correct Expression after looking through Heidi's post, is the following:-
=Sum(IIF(Fields!agenttype.value ="AgentType1",Fields!AgentType1.Total.Value, Cint(0)))
This worked perfectly thanks to the people who emailed me the specific link.
the link that helped me out was this
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=930085&SiteID=1
Hope you find this useful !
Sincerely,
Bernard
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
Wednesday, March 21, 2012
how to use select sum in sql...
can someone give me on how to use select sum with many fields...
thanks
here is my code but i dont know why it doesnt work...help pls
Dim Reader As PgSqlDataReader
Reader = db.GetSQLReader(String.Format("Select distinct urr_urinenget,sy_snm from urr inner join sy on sy.scd = urr_scd where urr_urinenget between {0} and {1} and urr_scd ={2} group by urr_urinenget,sy_snm", TDataCenter.db.TDataCenter.SingleQuatedStr(Date_from_new), TDataCenter.db.TDataCenter.SingleQuatedStr(Date_to_new), TDataCenter.db.TDataCenter.SingleQuatedStr(csd_code)))
Try
If Reader.RowCount > 0 Then
While Reader.Read
get_date = Reader.GetString("urr_urinenget").ToString()
product = Reader.GetString("sy_snm").ToString()
Dim objReader2 As PgSqlDataReader
objReader2 = db.GetSQLReader(String.Format("Select urr_urinenget,sum(urr_zurgsuu),sum(urr_zurgkin),sum(urr_curgsuu),sum(urr_curgkin),sum(urr_gurgsuu),sum(urr_gurgkin),sum(urr_turgsuu),sum(urr_turgkin) from urr where urr_urinenget={0} and urr_scd ={1} group by urr_urinenget ", TDataCenter.db.TDataCenter.SingleQuatedStr(get_date), TDataCenter.db.TDataCenter.SingleQuatedStr(csd_code)))
Try
If objReader2.RowCount > 0 Then
While objReader2.Read
'|||| New row
details_Row = details_Syouhinbetujunnbetu.NewRow
'|||| Assign values to column
Dim get_dd As String = objReader2.GetString("urr_urinenget").Trim()
Dim get_four_date As String = ""
Dim get_two_date As String = ""
' year
get_four_date = get_dd.Substring(0, 4)
' month
get_two_date = get_dd.Substring(4)
details_Row("urr_urinenget") = get_four_date + "年" + get_two_date + "月"
details_Row("urr_zurgsuu") = objReader2.GetString("urr_zurgsuu").Trim()
details_Row("urr_zurgkin") = objReader2.GetString("urr_zurgkin").Trim()
details_Row("urr_curgsuu") = objReader2.GetString("urr_curgsuu").Trim()
details_Row("urr_curgkin") = objReader2.GetString("urr_curgkin").Trim()
details_Row("urr_gurgsuu") = objReader2.GetString("urr_gurgsuu").Trim()
details_Row("urr_gurgkin") = objReader2.GetString("urr_gurgkin").Trim()
details_Row("urr_turgsuu") = objReader2.GetString("urr_turgsuu").Trim()
details_Row("urr_turgkin") = objReader2.GetString("urr_turgkin").Trim()
details_Syouhinbetujunnbetu.Rows.Add(details_Row)
End While
End If
Catch ex As Exception
Finally
objReader2.Close()
End Try
End While
End If
if all the fields have agreegate functions then the query is
select sum(a),sum(b) from tablex
if there is a field which do not need agreegation it has to be used for grouping
select sum(a),sum(b),c from tablex group by c
Let me know if you need any further clarifications