Help Understanding Aggregate Function Usage (COUNT) in SQL Without a Subquery

Hemanth_L

Hi everyone,

I'm currently working on creating reports and have been referencing the "Tickets" report for guidance. While reviewing the SQL query used in that report, I noticed that it's using an aggregate function (COUNT)—however, I don't see any subquery involved.

I'm a bit confused about how the aggregate function is being used in this context without a subquery. Could someone explain how this works or point out how the aggregation is being attached in such a case?

I've attached a preview of the SQL from the reference Tickets report below for context.

Thanks in advance!

lsw-fabian

Hello @Hemanth_L,
you can use grouping to do so without using a subquery. But in most cases, even for ticket charts, a subquery is a much better way to do so 🙂.

In your SQL preview it is visible via GROUP BY.

gabriel

This is the UI where these settings are made - they are available for columns:

By setting columns to be grouped by or aggregated, you can do stuff like counts without sub queries. But important to note:

When you use grouping, ALL columns must be grouped, aggregated or a sub query.
When grouped, you loose the option to open records from that particular field (in most cases it´s a list field). Reason being, that grouping aggregates rows - and, in the current implementation, r3 does not know which ID to open. For this reason, grouping only makes sense for readonly views or inputs for charts/graphs. That is why, sub queries are usually the better choice.