Support group by in query builder

[tlammer]

Requested by Willem. He would like to be able to get back counts on the different values in a field; so the query would say for field 1, this many records say x, this many say y, etc.

Willem's email:

I wonder if the ‘count’ function wouldn’t be more useful if it simulates the conventional database function in the way that results are output. Instead of a pop-up giving you the count, the count could be in a row in the results of the query (please see below).

I also wonder if ‘distinct’ is the best way to name this function, or if ‘group’ would be better. (the way I remember this in access is that you will get the count of the records in the field that ‘introduces different values’). Below it is the Text5 field that introduces different values while the accession number has the same value throughout the results.

(I have used the preparationproperty table as a ‘specimen audit’ table, meaning that whenever we conduct an audit of samples, we create another record in this table to say whether the sample is present or not)

I imagine that if we had this kind of display, we could perhaps include the aggregated output. So, if a sample had two audit findings, namely in Audit 1 it was present but in Audit 2 it was not present, it would be listed in a row of its own:

Present 42

Not present 86

Present; Not present 1

[maxpatiiuk]

This sounds like GROUP BY feature

It would be useful for future stats page features and advanced querying workflows

[bhumikaguptaa]

Philippe Verely, IRD, requested this feature.
https://discourse.specifysoftware.org/t/count-rows-from-the-query-builder/2787