Counting rows with a specific numerical value when using 'Group By'

Options
Tim_Stranack
Tim_Stranack Member
edited March 2023 in Scheduled Reports

Hi there - I'm new to Domo so apologies if this has been asked before and I have been unable to find the answer. I am using the "Group By' function in my ETL and in one of my aggregate columns I want to show the count of all the rows that have the numerical value of 80.

I have tried the formula COUNT(`ptstatus`= '80') and COUNT(`ptstatus`= 80) but in both cases it just returns a count of all of the rows not just those with a value of 80.

In the same Group By I have a similar formula working on a text field COUNT(`stage_name`= 'ACTIVE') and this works fine.

Can anyone tell me why the formula is not working on the numerical field?

Tagged:

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓
    Options

    Count is counting the number of non null values. You’ll want to return a value only if you want it counted. You can use a CASE statement in your COUNT to do this

    COUNT(CASE WHEN `ptstatus`=80 THEN `ptstatus` END)
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓
    Options

    Count is counting the number of non null values. You’ll want to return a value only if you want it counted. You can use a CASE statement in your COUNT to do this

    COUNT(CASE WHEN `ptstatus`=80 THEN `ptstatus` END)
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Tim_Stranack
    Tim_Stranack Member
    Options

    Thanks Grant - that solved the issue.

Categories