(I think this is more of a general SQL question but I can't figure out how to do this using aggregates so ended up trying to do it with SQL - if it can be done with aggregates that would actually be better for me!)
This is a contrived example so I can figure out how to have counts after things have been grouped.
I have 6 Players, 3 Teams and 3 Ranks
The teams are Red, Green and Blue.
The ranks are Rookie, Pro and Expert.
This is the test data.
I'm trying to query to get the following table
EDIT: added the following pic to make the goal clearer
EDIT: Ultimately this will be a query that could (in this terms of this example) list ALL teams (there could be many more) and the counts of the team members for each rank.
I tried to do this with an aggregate but couldn't create the counts individually so I then tried a SQL query thinking I needed something extra...but without much luck as all teams still show the same counts for each rank.
This is the SQL:
which gives me this result:
I've used LEFT JOINS on the Team -> Player as I want to include Teams that may not have any players and have 0, 0, 0 shown for the counts.
Can anyone see what I am doing wrong or what I need to do?
My SQL is not great.
I feel like I need to somehow include the Team.Id in the sub queries but can't see how to do it.
I've read pretty much every post regarding counts in aggregates but I can't see how they relate to my use case, where the counts are for things after the group bys.
I hope the above is clear. I've tried to reduce the problem down to something much less complex than my actual use case.
Hello Errol,
can you give a try with CTE Queries?
WITH common_table_expression (Transact-SQL) - SQL Server | Microsoft Learn
Thanks again for pointing me in the direction of the CTE queries.
I'm not sure if my solution is the correct way to use them but this is what I managed to come up with. (I feel like there must be a way to pass in the "Rank.Name" some how as the code is duplicated)
It gets the results I wanted though (and also gives me 0,0,0 if there is Team without any members, which is perfect)
Thanks again guys!
Hi,
Attached an oml with an example similar to what you explained.
Hope it helps
Regards
Hi @Bruno Marques
Thanks for the swift reply. I'm very grateful.
I think I may have over-simplified my example though making the problem unclear.
All of the entities will have extra attributes - I reduced them to just Names and Ids for clarity (so I'm not able to use static entities).
I'll have a closer look at the SQL to see if I can get some new ideas.
Thanks
This is the example project:
Hi @Fábio Vaz
I'm not familiar with CTE queries (SQL not really my thing) but I will read up on them now.
Many thanks,
Hi @Errol Chevannes ,
One possible way is to use a pivot sql exporting json and use a datagrid to show it.
Alternatively, with aggregates, the problem is not to count the players per team/rank, that's easy enough, but each count will be a separate row in your result set, you can't just keep adding more joins as the total number of ranks is not known upfront, so you're still left with how you will display this. That's a challenge anyway to just push it into a table, as table widget doesn't deal very well with a variable number of columns.
Have you put any thoughts into how to display, in terms of what widget to use ? Are you open to displaying ranks under each other as subrows of team instead of as columns ?
Dorine