I want to calculate percentage of value in Group by. My table data: id date artist track listenings 1 2023-01-01 C track_1 2016 2 2023-01-01 C track_2 3800 3 2023-01-01 B track_3 2311 4 2023-01-01 A track_4 4180 5 2023-01-01…
I have two tables like this: Table A id sum_value city 1 14 Paris 1 4 Paris 2 10 Berlin 3 68 Milan 4 51 London 3 2 Milan Table B id city geom 1 Paris MULTIPOLYGON(XXX) 2 Berlin MULTIPOLYGON(XXX)…
is there any way to implement top 1 result from each group without using ROW_NUMBER? table_a below has already 1 billion rows, so I want to use the most efficient way. And the below query is what I'm using. SELECT…
In my latest project I have been migrating my database which was using SQLite to a PostgreSQL database. I have been updating my SQL code to work with PostgreSQL, but have been struggling with one difference between SQLite and PostgreSQL,…
I want to find the best way to perform a server-side query to find JSON documents by some initial query criteria, then grouping all these documents by some field in the document and then reducing each group to only contain…
I have a result variable composed of 3 lists : var result = { type: [a,a,b,c,d,a,a,b], program : [P1, P1, P2, P1, P2, P2, P2, P1], reference : [ref1,ref2,ref3,...,ref8]} I want to group my reference by type and program to…
I understand that this query will produce numbers and cumulative tallies: SELECT number , SUM(number) OVER (ORDER BY number) cumulative_number FROM (SELECT 1 number UNION SELECT 2 UNION SELECT 3) inside GROUP BY number Result: number cumulative_number 1 1 2…
I am trying to find all the rows in my table, which a certain column is duplicated in them. and identify them by the unique id column that each row has. I want to delete them and leave only one…
I'm trying to group rows when it starts with 1 and has consecutive 0s, and stop grouping once it meets another 1 or 2. Here is an example: (column 'group' is what I expect to see) +-----+------+-------+ | seq |…
I have a MySQL database table that looks like below: +----------+------------+---------------------+ | name | event | created | +----------+------------+---------------------+ | Player1 | Logged in | 2023-02-14 10:05:00 | | Player2 | Logged in | 2023-02-14 10:05:30 | | Player3 |…