Count table records by column values

Alex Kornienko

Hi there! I need help with creating an API endpoint.

I have a report table where I reference other tables. In particular, I have Rating_1 column, which shows Rating values (integers, 1 to 5) selected by users. I'm trying to create an API endpoint that will return a count of report records for each rating value, even if it's 0 (which means no report records contain this Rating_Value in the "Rating_1" column.
[Знімок екрана 2021-12-15 о 14.15.44.png]
For example:

"experiment_id" 266
"option_id": 368
"Rating_Value" : 1
"reports_count": 2

"experiment_id" 266
"option_id": 368
"Rating_Value" : 2
"reports_count": 0

"experiment_id" 266
"option_id": 368
"Rating_Value" : 3
"reports_count": 5

etc.
[Знімок екрана 2021-12-15 о 14.16.04.png][Знімок екрана 2021-12-15 о 14.16.14.png][Знімок екрана 2021-12-15 о 14.15.19.png]
I suppose this could be done with Aggregate, but I couldn't achieve the result I need. Can anyone help me?

Michael Udinski

Hey there, this is how you would do it. Use an aggregate return type to get your groupings and count then an Addon to pull the value of your rating table:



Alex Kornienko

Awesome, thank you !

Raynell Bell

can a video be produced on how to do this? I am new to the tool and am a visual learner.. thanks

David Lundvall

Does anyone have an example of this recorded?