How can I count occurrences grouped by a given field?

alexbdomo
alexbdomo Member
in Magic ETL

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.

image.png


Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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#`)
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach

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

    Screenshot 2023-02-18 at 3.58.08 PM.png


    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • alexbdomo
    alexbdomo Member

    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
    GrantSmith Coach
    Answer ✓

    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#`)
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories