How can I count occurrences grouped by a given field?

alexbdomo

Hello Domo users,

I need to count the number of occurrences grouped by a given field. See the example. I tried a beast formula, but I cannot get the total number of occurrences. Is there a way to do it using a beast mode? Or is there another way to figure out the number of occurrences? Maybe using Magic ETL? I would prefer not to add a Magic ETL layer and a beast mode would be much cleaner.

Thank you.

GrantSmith

Ok I think I understand. You want the count of the IDs on each line of the ID.

For that you can utilize a window function

 SUM(SUM(1)) OVER (PARTITION BY `ID#`)

GrantSmith

You can aggregate your Occurrences in the analyzer by selecting SUM from the aggregate option when you select the column.

alexbdomo

Hi Grant,

thank you for the response, but that's not exactly what I was looking for, or at least, I am not getting the outcome I was expecting. Let me clarify a bit more; going back to my example, I want to obtain the number of occurrences based on the ID#, so for ID# = 100, I only have 1, for ID#=500, I have 4, etc.

I tried your suggestion with sum and count, but don't get it the correct number.

GrantSmith

Ok I think I understand. You want the count of the IDs on each line of the ID.

For that you can utilize a window function

 SUM(SUM(1)) OVER (PARTITION BY `ID#`)