Skip to content

Tag: aggregate-functions

SQL query to aggregate result of a column comparison

I have a SQL (Postgres) data structure that represents games for a group of matches. Table below shows that there have been 3 matches played (each consisting of either 2 or 3 games). match_id player_one player_two p1_game_result p2_game_result 1 player1 player2 12 10 1 player1 player2 3 11 1 player1 player2 5 9 2 player1 player3 11 2 2 player1

PostgreSQL: Create array by grouping values of the same id

Given the following input data: id category 1 A 1 B 2 A 2 R 2 C 3 Z I aim aiming to get the following output table: id categories 1 {“A”,”B”} 2 {“A”,”R”,”C”} 3 {“Z”} using the following query: But what I get is the following table: id categories 1 {“A”,”B”,”R”,”C”,”Z”} 2 {“A”,”B”,”R”,”C”,”Z”} 3 {“A”,”B”,”R”,”C”,”Z”} How can I obtain

Invalid group by expression error when using any_value with max and window function in Snowflake

I was given a query and I am attempting to modify it in order to get the most recent version of each COMP_ID. The original query: I then attempted to use a window function to grab only the highest version for each comp_id. This is the modified query: When attempting to compile the below error is given: SQL compilation error: