Skip to content

    • Magic ETL

    Magic ETL

    How can I count occurrences grouped by a given field?

    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


    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Best Answer

    • 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

      1.  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

    • 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!**
    • 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.

    • 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

      1.  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!**

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories