Counting the number of times a single select value occurs in a table

Baserow Help
,
#1

Hi,

I have a table with music bands. Each band has a genre that is shown in a single select field:
image.

I want to add a new field with a formula that counts the number of times each genre appears in the table. In the screenshot above, this would be 2 for Rock and Soul and 1 for Country. However, the count function only returns the number of items in an array which is always 1 in my case (because a band has 1 genre).

Is this possible with a formula? I am actually looking for the equivalent of the COUNTIF function in a spreadsheet.

Regards
Frederik

#2

Hi @frederikdc you can use the filter formula function to filter down these lists like so:

count(filter(field('Genre'), totext(field('Genre'))='Rock')

#3

Hello Nigel,

Thanks for the reply, but the formula does not work like I expected. It shows me that the genre Rock appears 0 or 1 time in the row. But I would like to know how many times a genre appears in the table (or view).

image
image1226×412 67.2 KB

So, based on the screenshot above, this should be 2 for Rock and Soul and 1 for Country. I am not sure if this is possible with a formula, since formulas are only executed row by row (I think)

Regards
Frederik

#4

Ah I see now. Yeh your only options here are:

  • Make a formula field per Genre and use the footer aggregation sum for each field (you can mouse over the footer of a field to set an aggregation)
  • Link to every row in this table in a new “Summary” table, and write a formula over that summary row.

Really what this sort of query wants is some sort of group by/pivot view which is on our roadmap.

Alternatively we have vague plans to add some sort of formula function that lets you do proper joins without having to use a link row field. Or perhaps the ability to set a link row field to join using a formula instead of being manual.

#5

Thanks for offering the options, but they are a bit too complex for what I want to achieve.

I just like to see if every genre is represented more or less equally. So, I created a new view, added a field where I convert the single-select to text and sort on that field. Using a different color for every genre also shows me if a genre occurs a lot or not.

image
image814×304 50.1 KB