Performing SUM on GROUP in SQL 2005 Reporting Services

I am using SQL 2005 reporting services accessing a SQL 2000 server (I'm going to migrate the data over later).

The program I am having is that I am grouping on a field but when I try and sum on another field, it adds up all of the items instead of those just being displayed.

For example, this would be a collection of records:
partnumber;note;price
part1,note1,5.0
part1,note2,5.0
part1,note3,5.0
part2,note1,3.0
part2,note2,3.0

Well I want it to just sum up the 5 and 3 once and give me 8, instead it give me 21.

I'm looking to just perform a SUM on the items that are actually displayed on the screen.  I have searched all over and found others with similar problems, but no solution.

I have been able to do a SUM just on the ReportItems!textbox.Value, but the problem with that is I can only place the results on header or footer.
ritetrackAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:


do you mean?
select a.partnumber,a.note,sum(case when a.note=b.minnote then a.price else null end) as totalprice
from yourtable as a
inner join (select partnumber,min(note) as minnote from yourtable group by partnumber) as b
 on a.partnumber=b.partnumber
group by a.partnumber,a.note
order by 1,2
0
ritetrackAuthor Commented:
No, this really needs to be done in the reporting services rather than the SQL statement.  The reason is that in some cases your SQL statement might work, I have other cases that I would group on the item number and only want to grab the value once, but I would want to show the comments in the details.  For example, the results of the report from the original dataset I put might look like:
------------------------------------
part1 - $5
 - note1
 - note2
 - note3

part2 - $3
 - note1
 - note2

Total of parts: $8
------------------------------------
Where this can come more into play is when I have multiple groups (i.e. group by region, then by customer, then show the detail for a customer)
0
itstherideSystems AdministrationCommented:
not sure if this is what your after...

sum(columnName, "grp_Customer") where "grp_Customer"  is the name of your group. do this on the group footer.


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.