Aggregate over grouping from group by

[luifire]

Hi people,

Generally, I want to have the aggregate function to aggregate within a group from group by, instead it "group by" does not seem to do anything, when I aggregate.

Example:

Country, CustomerID
US, 1
US, 2
DE, 3
Fr, 4
Fr, 5

Now SELECT Country, COUNT(*) FROM table GROUP BY Country should yield

Country, COUNT(*)
US, 2
DE, 1
FR, 2

Instead it yields "The expression Country is not an aggregate operation. Only aggregates (min, max...) can be selected from queries with groupings."

If I do SELECT COUNT(*) FROM table GROUP BY Country, the GROUP BY part does not do anything, i.e. this query: SELECT COUNT(*) FROM table yields the same, i.e.

COUNT(*)
5

Am I doing something wrong? Is there a better way to do that? I want to have this as histogram in my dashboard.
Practical applications: seeing how often particular devices have connected.

Similar discussions:
#1501

[liammclennan]

Hi @luifire ,

Aggregate functions are always applied to groups, if the query has a group by.

Seq does not have tables because it is a log not a relational database. To query the log you should select from stream.

It automatically includes the group labels, so your query should be:

select count(*) from stream group by Country

The docs explain all of the different things you can do.

[luifire]

Thanks, my bad.

This was not about from stream part, but the group by part. I grouped by a none existing identifier, i.e. by X.id instead of X.Id. Therefore the group by did not have an effect and it looked as if there was no grouping.

[liammclennan]

Hi @luifire

Glad you found a solution.

The group by still worked. The non-existent property will evaluate to null for each event, hence everything is in the null group, and you will see that in the result.