I am struggling with WINDOWS FUNCTIONS / GROUP BY and the question I created that I want to answer: From the chat table, WHICH country chatted the most about WHICH game?
So, I want a table that has the country, the game and the chat COUNT(login) OF the country that chatted the most about THAT game…
Maybe the answer is that this question is ridiculous…
With a query that uses GROUP BY I can isolate the COUNT of login(s) for each game ONLY if I specify the country in a WHERE clause. As soon as I try to use a WINDOWS function to PARTITION BY country then the COUNT function is not aggregated by game even when using ORDER BY game.
I realize that I don’t fully understand the WINDOWS FUNCTIONS - I re-read what I learned about them and PARTITION BY will show the column for every aggregation…
EDIT: I have been experimenting and this gets me close:
SELECT COUNT(login), game, country
WHERE game != ‘game’ AND country != ‘country’
GROUP BY game, country
ORDER BY COUNT(login) DESC;
I have to use the != ‘game’ and != ‘country’ because these fields actually exist…not just as headers.