This query with case is possible to create in the aggregate?

Im not understanding if is possible to do a more complex query using the aggregate. I have movies and movie genres. I'm showing the movie genre and at right the number of movies that exist in that movie genre. This works using a group by on the movie genre and counting the movie ids. However, is possible to use the agregate to show, for example, the count of each movie for the movie genres "Comedy" and "Action" but for the other genres instead of show the count for each genre show the count of the other movies that don´t belong to comedy or action as "Remaining"? like:

Action  10
Comedy  7
Remaining  15

It should be neessary select the movie genres when the movie genres are 'Action' and 'Comedy', otherwise is necessary to count all the other genres as "Reamining".

Solution

yes, you can use something like this:

1) Add New Attribute and type this expression: 

SyntaxEditor Code Snippet

If(Z_SITUACOES_CREDITOS.Codigo="A" or Z_SITUACOES_CREDITOS.Codigo="D",Z_SITUACOES_CREDITOS.Codigo,"Remaining")


2) group by attribute1

3) do a count by Id:

4) do an assign to screen of the Attribute1 + " "+ Count2

I hope it could helps you!

Thanks and Best Regards,

Nuno Pereira

Solution

Thanks, it works but can you explain the part of the if after the "," (MovieGenre.Movie.Genre, "Remaining")? Im not understanding how it works.


if(MovieGenre.Movie.Genre = "Action" or  MovieGenre.Movie.Genre = "Comedy", MovieGenre.Movie.Genre, "Remaining").

John K wrote:

Thanks, it works but can you explain the part of the if after the "," (MovieGenre.Movie.Genre, "Remaining")? Im not understanding how it works.


if(MovieGenre.Movie.Genre = "Action" or  MovieGenre.Movie.Genre = "Comedy", MovieGenre.Movie.Genre, "Remaining").

This will construct an attribute which if genre was "Action" or "Comedy", then use the actual database genre as an attribute result, if not, use "Remaining" as the attribute result of the expression.