Dynamic Statistics for distinct values with counts

[bronwyncombs]

Requested By: Corinna @ CSIRO

This is an enhancement request for the Statistics page. It would be very helpful if you could create a stat box that is locked to showing the list of distinct values and the count of those distinct values in the system. For example, we have a collection code in our wildlife instance that defines if a specimen is a bird, mammal, reptile etc. Right now, we have to create a separate query for each value of collection code and add that to the stat box for each row to appear. It is also not dynamic, so if we have new values they don't automatically appear.

(Note this is a problem with the OOTB queries you've provided e.g. for the preparation stat box it does not refresh if we have a new prep type, we have to remember to add that row into the stat box)

What we would like is an option to designate a stat box to a simple count/group by query e.g.

SELECT [field], count[field] FROM [table] group by [field]

So the user can simply select any field from any table, and then that stat box shows the values and their counts e.g.

Mammals	12345
Birds	23456
Reptiles	987

When the stats page gets refreshed the underlying code for this box is re-run so any new values appear automatically

Mammals	12345
Birds	23456
Reptiles	987
Amphibians	1

[maxpatiiuk]

This is already kind of implemented, except right now this only exists for a few hardcoded tables (Prep Types, Taxa ranks, Geographics) Lifting that restriction to letting user pick any field as the distinct value would resolve this issue