Performing SUM on GROUP in SQL 2005 Reporting Services

Posted on 2007-04-10
Medium Priority
Last Modified: 2010-03-15
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:

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.
Question by:ritetrack
LVL 50

Expert Comment

ID: 18885501

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

Author Comment

ID: 18886439
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)

Accepted Solution

itstheride earned 1000 total points
ID: 21345356
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.


Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question