More grouping questions - sum of maximum

I have a table with two groups, unit and domicile. Structure appears as follows:

GH1 - Domicile Group: Bentwood
GH2- Unit Group: 200
Detail: LegID Unit ID Miles TripMiles
1 200 25 150
2 200 75 150
3 200 50 150

Subtotal for GH2 Count(Fields!legID.value) Sum(Fields!Miles.value) First(Fields!TripMiles.value)
3 150 150

This works, what breaks is the subtotal for GH1. on the TripMiles Field.
It doesnt like Sum(First(Fields!Tripmiles.value,"Unit_Group")), nor can I do Sum(ReportItems!Trip_group.value) I get the error that an aggregate cannot occur on a report item unless it is in the page header or footer.

Of course if I try Sum(Fields!TripMiles.value,"Domicile_Group") I get 450 which is incorrect.

I've tried making First(fields!tripmiles.value) a calculated field which I just then drop into the header of the Unit group<GH2>. This works perfectly until I then try to Sum that field in the Domicile group. I can create the expression but once I click ok, VS2005 reports that it must shut down and crashes to desktop, when I do a recovery all my changes are lost.

<by the way this crash always occurs when I create a calculated field that uses First/Max or Min, then try to use that field in another calculation. Fields based on IIF work without issue>

To clarify, I want to take the TripMiles Column and for The Unit group show the first value that occurs for that unit as the value.

In the Domicile Group, I want to sum all of those Unit Values.
if unit 200 had 3 occurances of 150, my Unit group would have a value of 150 <not 450>
if Unit 300 had 4 occurances of 100, my Unit group would have a value of 100 <not 400>
My total for the Domicile would be 250 <not 850>

Thank you for any assistance.

I can't think of a way of doing this but surely in your case the subtotal at the Domicile level for TripMiles column should equal that of the Miles column.

I would cheat and just put Sum(Fields!Miles.Value) in the subtotal for the TripMiles column in the Domicile group footer.

|||

Bah I jinxed myself using a perfect scenario in that mielage column didnt I <sigh>

Yes I agree with you in this particular instance with the data I displayed. TripMiles never change, it is a base amount for the order. I should have added two more rows to show that the two columns do not always match.

Let's pretend that the TripColumn was labeled something else, Like fuel Consumed.

If I could do an aggregate on the Max/min/first of that field I would be set :(

Edit: OK, I found another thread similar to my situation here :http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=106424&SiteID=1

Where it is stated that at the time that post was made, One year ago last week, you could not do an aggregate of an aggregate and gave a work expression that is one I've tried but errors out.

They then stated to 'modifey' the query to insert nulls at the duplicate. So in my example I'd have
150
null
null
null

How do I do that?

|||

You can use the SQL 2005 ROW_NUMBER() aggreagate function as follows:

SELECT LegID
, UnitID
, Miles
, TripMiles = CASE ROW_NUMBER() OVER (PARTITION BY LegID ORDER BY LegID) WHEN 1 THEN TripMiles ELSE NULL END
FROM YourTable

|||what other solution, if i'm using SQL 2000? what other method to achived the above reasult?|||

Code Block

SELECT LegID
, UnitID
, Miles
, TripMiles = CASE LegID

WHEN (SELECT MIN(LegID) FROM YourTable)

THEN TripMiles

ELSE NULL

END
FROM YourTable