Sum distinct field by group

[giggles7840]

oracle 11g/xir2

how do i: sum(distinct field, group)

 

[lbass]

You could sort by the field in question, and then use a running total where you sum the field, evaluate on change of the same field, reset on change of group. Place the result in the group footer.

-LB

 

[giggles7840]

I did that just after I posted this. Although the numbers come out correctly, I need to actually have the formulas in the header because the user will want to drill into the details of those numbers.

any ideas?

 

[lbass]

They would be drilling into the group. If you place the groupname in the group footer along with the running total, you can drilldown on the groupname and see all of the detail.

-LB

 

[giggles7840]

sorry i should have explained that they only want to see a grand total summary upon first look and then drill into that. If i put the running total into the group header it will give the breakdown by each group rather than showing only the last number of the running total.

 

[lbass]

What? You cannot place a running total in a group header. I suggested dragging the groupname into the group footer, and placing the running total there.

Do you have multiple groups? You can still use group headers for higher order groups and the group footer for this one.

-LB

 

[giggles7840]

i misread what you had and then i mistyped what i did. i did put it in the GF. Although even doing that it still didnt work as needed. Let me try to explain again what the user is asking for.

The user wants to see:

# of distinct ID's distinct $ paid
Total 40 3535
Avg Paid 88.38

I can get all of this in the report footer which of course does not allow drill down.

Then user wants to be able to click on 40 from the total and drill in to see those 40 ppl.

If i create a group on ID and put in the group name in the GF and I see the distinct id's. i then put in the amount field to give me the distinct amount per distinct id. then when i add in the running total as you suggested it gives the break down as the amount column does. however if i edit the running total to sum on id and change on goup and never reset then i on the last id i see i will get the final total that i am looking for. the problem that i have is that the user doesnt want to see any of the group break downs. I want tot make sure there isnt another way to give them what they want before going back to them and telling them tough they have to have that if they want drill down.


maybe i am still not understanding what your suggestion is?

 

[giggles7840]

oh i think i just got it to work..... testing now

 

[giggles7840]

yup worked. i had to do a formula:
if id is not null then 1 else 0.
then i used that as a group.
then i could pull my distinct group into GF1 and the running total which is sum on id, eval on field id and reset on new formula group.