Incorrect Sums when joining tables

Question

Incorrect Sums when joining tables

tsmith-960032

SSCrazy

Points: 2518
More actions
August 1, 2010 at 1:47 am

#224782

I have joined several tables. I need to show data from several records of Table 2 or 3 etc. in the details. I also want to sum up one of the columns in Table 1. Because there are several matches from the other tables, my column 1 from table 1 repeats, thus giving me a column sum that varies depending on how many records are pulled from the other tables, and of course is much larger than the real sum.
Can someone help with this? Thank you.:crazy:

Viewing 7 posts - 1 through 6 (of 6 total)

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

tsmith-960032

In order to get a tested solution to your question, please post table definitions, sample data and required results. To assist you in doing that please post as outlined in the article which can be accessed by clicking on the first link in my signature block.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

tsmith-960032 SSCrazy Points: 2518 More actions · Answer 2

bitbucket-25253 (8/1/2010)
tsmith-960032
In order to get a tested solution to your question, please post table definitions, sample data and required results. To assist you in doing that please post as outlined in the article which can be accessed by clicking on the first link in my signature block.

What I am describing above is a known SSRS issue and is not isolated to my report. When joining tables where the second returns more than one record, if you try to sum a field in the first table, the result depends on how many records were returned from the second table. I was just wondering if someone had found a simple workaround. I don't think this type of issue which is general merits my posting my entire report logic. Either I didn't make myself clear, or you didn't understand what I was asking.

DaveInIowa SSC Journeyman Points: 87 More actions · Answer 3

You can do this in your query ...

SUM(t1.fieldtosum) OVER (PARTITION BY t1.groupfield) fieldsummaryvalue

veeren4urs SSCrazy Points: 2763 More actions · Answer 4

Hi,

In 2008 Group varibles will help for this declare the group variables and check the sum.

Thanks,

Veeren 🙂

Thanks & Regards,
Veeren.
Ignore this if you feel i am Wrong. 😉

tsmith-960032 SSCrazy Points: 2518 More actions · Answer 5

veeren4urs (8/5/2010)
Hi,
In 2008 Group varibles will help for this declare the group variables and check the sum.
Thanks,
Veeren 🙂

Thank you. I am already looking at this. However I have not yet been able to make this work with regards to usage, syntax, etc. Looks like I need to buy more books...

veeren4urs SSCrazy Points: 2763 More actions · Answer 6

Alright All The best. 🙂

Thanks & Regards,
Veeren.
Ignore this if you feel i am Wrong. 😉