Min, Max, Average and Median

[BreffniK]

Thanks in advance for reading my question.

If I were to consider all the parent records in my database, how may I calculate the Minimum, Maximum, Average and Median number of children records?

So for example, if there is a parent record in my database which has 0 (zero) children, I'd like to see 0 (zero) returned to the Minimum field.

If there is a parent record in my database that has 10 children records, and that is the most of all the parent records, I'd like to see 10 returned to the Maximum field.

Same for Average and Median......

Children records are linked to their parent using record.parent_id.

Please let me know if I need to further clarify.

Any advice or suggestions are greatly appreciated!!!

 

[synapsevampire]

Group by the parent ID.

Create 4 formulas:

minimum({table.field},{table.paemrt})

...etc...

-k

 

[BreffniK]

Thanks K,

What's "table.paemrt"?

 

[lbass]

It sounds to me like you want the maximum, minimum, average, and median of a count summary. If you are using version 9.0 or higher, you could use a command (database->database expert->your datasource->add command) to return the count per parent record, as in:

select table.`parent`, count(table.`child`) as cnt
from table
group by table.`parent`

Then you could place these fields in the detail section and right click on {command.cnt} and insert summaries (maximum, minimum, average, median).

-LB

 

[BreffniK]

Thanks LB.

I did so, but it looks like they are only considering Parent records that have children and are ignoring the rest (those that have none).

I'm not sure what I'm doing wrong.....Any advice?

 

[lbass]

Please post your command.

-LB

 

[BreffniK]

select pr.parent_id, count(pr.id) as cnt
from pr
group by pr.parent_id

 

[lbass]

Are you sure it isn't the child record that contains the parent ID (which references the ID of the parent record)? Should it be:

select pr.id, count(pr.parent_id) as cnt
from pr
group by pr.id

Otherwise I'm not sure why this would happen. When I tested this, using a field that could be null for the child record, all parent records showed up.

-LB