Calculating Totals

Question

Calculating Totals

P.Prashanth Reddy

Valued Member

Points: 54
More actions
March 11, 2009 at 12:37 am

#200765

I have two tables called Resource, and Comment
table structure is
1)Resource
EntryNoint
ResourceNovarchar(50)
JobNovarchar(50)
Quantitydecimal(18, 0)
2)comment tablr
CommentNoint
CommentLineNoint
commentntext
Relation between two table is EntryNo in Resourse table and the commentNo in comment table are the same and one entry number will have n number of comment in comment table
my job is to create a .rdl report with this two bale with using below query
SELECT Rsource.EntryNo, Rsource.ResourceNo, Rsource.JobNo, Rsource.Quantity, Comment.comment, Comment.CommentNo,Comment.CommentLineNo
FROM Rsource left outer JOIN
Comment ON Rsource.EntryNo = Comment.CommentNo
its getting fine but when iam caculating the total quantity for perticular entry no the total iam getting is wrong because of if quantity of perticular entryno is say 40 and this entry no is having 4 comments in comment table then the total quantity is 200(it is adding 4 times because i have 4 comments againest this entry no)
Please suggest me how to get the exact total with out any duplication

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Rick Todd Hall of Fame Points: 3302 More actions · Answer 1

P.Prashanth Reddy (3/11/2009)
I have two tables called Resource, and Comment
table structure is
1)Resource
EntryNoint
ResourceNovarchar(50)
JobNovarchar(50)
Quantitydecimal(18, 0)
2)comment tablr
CommentNoint
CommentLineNoint
commentntext
Relation between two table is EntryNo in Resourse table and the commentNo in comment table are the same and one entry number will have n number of comment in comment table
my job is to create a .rdl report with this two bale with using below query
SELECT Rsource.EntryNo, Rsource.ResourceNo, Rsource.JobNo, Rsource.Quantity, Comment.comment, Comment.CommentNo,Comment.CommentLineNo
FROM Rsource left outer JOIN
Comment ON Rsource.EntryNo = Comment.CommentNo
its getting fine but when iam caculating the total quantity for perticular entry no the total iam getting is wrong because of if quantity of perticular entryno is say 40 and this entry no is having 4 comments in comment table then the total quantity is 200(it is adding 4 times because i have 4 comments againest this entry no)
Please suggest me how to get the exact total with out any duplication

You have a couple of choices. Do you need to be doing the join to the comment table when you're doing your Quantity totals? If not, then just do the total in there, if yes, then do this:

SELECT Rsource.EntryNo

, Rsource.ResourceNo

, Rsource.JobNo

, SUM(Rsource.Quantity) AS WrongQuantity

, COUNT(1) AS NumberOfRows

, SUM(Rsource.Quantity)/COUNT(1) AS ProperQuantity

, Comment.comment

, Comment.CommentNo

, Comment.CommentLineNo

FROM Rsource left outer JOIN

Comment ON Rsource.EntryNo = Comment.CommentNo

GROUP BY Rsource.EntryNo

, Rsource.ResourceNo

, Rsource.JobNo

, Comment.comment

, Comment.CommentNo

, Comment.CommentLineNo

Let me know if that doesn't meet your needs,

Rick Todd

P.Prashanth Reddy Valued Member Points: 54 More actions · Answer 2

Thanks Rick for the reply.

But I must join the comments table. The problem is that I cannot make any changes to the following query.

SELECT Rsource.EntryNo, Rsource.ResourceNo, Rsource.JobNo, Rsource.Quantity, Comment.comment, Comment.CommentNo,Comment.CommentLineNo

FROM Rsource left outer JOIN

Comment ON Rsource.EntryNo = Comment.CommentNo

Whatever, I am allowed to do is in RDL file only - by using wicked code or using hacks.

Thank you.

Rick Todd Hall of Fame Points: 3302 More actions · Answer 3

P.Prashanth Reddy (3/11/2009)
Thanks Rick for the reply.
But I must join the comments table. The problem is that I cannot make any changes to the following query.
SELECT Rsource.EntryNo, Rsource.ResourceNo, Rsource.JobNo, Rsource.Quantity, Comment.comment, Comment.CommentNo,Comment.CommentLineNo
FROM Rsource left outer JOIN
Comment ON Rsource.EntryNo = Comment.CommentNo
Whatever, I am allowed to do is in RDL file only - by using wicked code or using hacks.
Thank you.

Ahh, that's much more interesting! Do you have to have that as your dataset in the RDL, or can you encapsulate that query in another query, for example?

Rick Todd