Mysql – SQL Ifelse by Group
I have SQL table such as this: TABLE1 STUDENT SCORE SCOREVAL 1 PASS 10 1 MOD1 4 1 MOD2 5 2 MOD1 4 3 MOD2 2 3 MOD2 1 3 MOD1 9 3 ER 1 4 MOD2 10 4 ER…
Mysql – Get row of data for MAX value over GROUP BY and JOINS
I am working with the ml100k dataset to write a query that can fetch me the most rated movie for every age. Here's how my tables are defined. users id | age | gender | occupation | zipcode ratings userid…
mongoDB aggregate method find movies from last 20 years
I am trying to get all the movies from the latest 20 years of the document. I am trying like this but doesnt work. It´s the first time I use mongoDB so I am a bit lost, it may be…
Min dates from group by postgresql
I have table with data: create table measure_app_scalemeasurement ( store_code text, certificate_id text, audit_ending date); insert into measure_app_scalemeasurement values ('K010','vwv', '10.12.2023'), ('K010','cert1','12.12.2023'), ('K054','vwv', '14.12.2023'), ('K054','cert1','20.01.2024'); I want to select min audit_ending date for each market store_code, also getting its corresponding…
Postgresql – Add an aggregate over full table outside the buckets to every bucket row
I have a query that filters the emails of people who "have a quantity" bigger than the median over all quantities found in the table: SELECT AVG(quantity) AS avg_quantity, email FROM table GROUP BY email HAVING AVG(quantity) > (SELECT PERCENTILE_DISC(0.5)…
MySQL Joining same table multiple times makes count go weird
Edited to clarify and simplify I have the following query select Signups.user_id, Count(timeoutTable.user_id) as timeoutCount, Count(confirmedTable.user_id) as confirmedCount from Signups left join ( select * from Confirmations where action = 'timeout' ) as timeoutTable on signups.user_id = timeoutTable.user_id left join…
How to query table Using Group By, Limit and Sum in MySQL
A_id B_id L M N 1 1 1 1 0 1 2 4 3 2 1 3 2 2 1 2 4 2 2 1 2 5 3 2 1 2 6 1 1 0 3 7 4 3 1…
Mysql – How do I group by values in multiple columns in SQL?
Imagine a table that looks like | sender_ID | Receiver_ID | message_ID | , say table name is **tb** now I want to write an SQL query that gives me : user_id, Total messages exchanged by that user . So…
Postgresql – psql: How can I get a single json column in aggregation after a group by query
I am trying to get a single row for each group by clause in the query. Each SELECT output has a json in it. I am trying this: SELECT MIN(C.json_column->'key') AS Key, A.field1, B.field2 FROM json_table AS C LEFT JOIN…
Postgresql – Postgres query using filter and counts grouped by month
Below is the data table - del_no | Pkt | direction | Env | start_datetimestamp | ---------+---------+------------+-------+---------------------+ H_00002 | 02 | SOUTH | PROD | 2022-10-29 16:20:57 | E20 | 20 | NORTH | PROD | 2022-10-30 16:41:37 | H_00002…