Skip to content

Tag: aggregate-functions

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:

Access Bare Columns w/ Aggregate Function w/o adding to Group By

I have 2 tables in postgres. users auth0_id email 123-A a@a 123-B b@b 123-C c@c auth0_logs id date user_id client_name abc-1 021-10-16T00:18:41.381Z 123-A example_client abc-2 … 123-A example_client abc-3 … 123-B example_client abc-4 … 123-A example_client abc-5 … 123-B example_client abc-6 … 123-C example_client I am trying to get the last login information (a single row in the auth0_logs table

SQL total for each row combination

Are there any types of aggregate functions that could help calculate the following? If I have the following data, I want the sum total for each duplicated row. The output would provide total number each row occurs: Answer Why not good old group by and count?

I tried solving this problem by using MAX function but get the result . Is there a way to solve it by using any other function?

I need to get “second updated_time” for each order and sorting them by “update_time”. I wrote the following query, which is not giving any output. Suppose, we have to calculate get the time difference between second updated time and first updated time against each order_id ,if we sort the table on update_time (same question but extended) Server is Mysql. The