Count value in aggregate column
Application Type
Reactive

Hi community,

In our application we have some kind of dashboard where some counting is happening as you can see in the image 

below.

Our entity contains one attribute that contains the status of an approval (Approved):

0 = Request is pending

1 = Request is approved  

2 = Request is declined


Suppose there are 36 records of which 20 have the approved value (1) and 10 have the declined value (2) and 6 have the pending value (0). I want to show 20 as the total number of approved requests, 10 as the total number of declined requests and 6 on pending. Is there any way in aggregates to calculate the amount of a certain status or do I need SQL for this as I normally would do? 


Thanks in advance!

Bart

mvp_badge
MVP
Solution

Hi Bart,

I have attached the sample oml for you as an example.

Hope this helps:

Regards,

Manish Jawla

StatusCount.oml

mvp_badge
MVP

HI Barat,

Please check the below article and let me know if that helps:

https://success.outsystems.com/Documentation/11/Developing_an_Application/Use_Data/Query_Data/Calculate_Values_from_Grouped_Data#:~:text=In%20OutSystems%2C%20you%20can%20use,on%20groups%20of%20identical%20data.&text=Once%20you%20group%20or%20use,only%20output%20of%20the%20aggregate

In your case you can group by status column and apply count on the Id column to get the counts for each status like the example below, i did for description:

Regards,

Manish Jawla


I think you mean like this right? That seems to work.

mvp_badge
MVP


I am still not sure how to show them as seperate values on my screen. I am sorry if this may sound stupid. I am coming from a front-end background and I am totally new to backendish things.

I am trying to use an assign on the OnAfterFetch but I'm not sure how to get the different status counts in a separate variable

mvp_badge
MVP
Solution

Hi Bart,

I have attached the sample oml for you as an example.

Hope this helps:

Regards,

Manish Jawla

StatusCount.oml

Hi Manish,

Thanks for sharing the oml. Is this a best practice? It seems silly to create 3 aggregates for this. Is this the only way of doing this in aggregates? Would it be better yo use SQL for this otherwise?

mvp_badge
MVP

Hi Bart,

Aggregates are auto optimized by platform, thus do not impact the performance of your application, we can achieve the same via SQL as well but this one is quite straightforward and easy that's why I shared this example with you.

mvp_badge
MVP
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.