Mysql – SQL SELECT . Rows to columns
I have a table with two fields. The 1st is a 'key' and the 2nd field can have only value 'A' or 'B'. I would like, with a SELECT, to retrieve a recordset that, grouping by the 1st field, have…
Postgresql – How can I make this aggregation query fast for my needs?
Describing the scenario I have this table called event defined in PostgresSQL that, among some not relevant ones, basically has 5 columns: id (integer): primary key of the table date (timestamp): date when the event occurred fk_type (integer): foreign key…
Postgresql – Getting only the highest counts per type in Postgres
Say I have a table with kids and their toys. CREATE TABLE kids_toys ( kid_name character varying, toy_type character varying, toy_name character varying ); kid_name toy_type toy_name Edward bear Pooh Edward bear Pooh2 Edward bear Simba Edward car Vroom Lydia…
Mysql – Get the sum of a column for exactly n rows
I have a table like this: CREATE TABLE test( id INT AUTO_INCREMENT PRIMARY KEY, server_id VARCHAR(15) NOT NULL, secret_number INT NOT NULL, processing_status VARCHAR(15) DEFAULT 'UNPROCESSED', ts TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ); I want to get the SUM(secret_number) of…
Postgresql – SQL: two aggregate functions without duplicates
How to use two aggregate functions (for example double string_agg or just sum), but ensure that the results do not contain duplicates caused by the other aggregate function (caused by second join)? I use PostgreSQL. Example I have three tables:…
Mysql – Generating json data and filtering on matching condition from a specific table
Table "ORDER": ID ORDERN_NUM 1 123 2 321 3 456 Table "RECEIVERS": FIRST_NAME ID LAST_NAME Pete 1 Tyler Sarah 1 Bowden Bart 2 Simpson Table "PRODUCTS": TYPE ID Towel 1 Pen 1 Spoon 2 We can say "FIRST_NAME" and "TYPE"…
Can Postgresql merge users w/ multiple refs & count their collective assets?
There is a set of users. A person can have multiple users, but ref1 and ref2 might be alike and can therefore link users together. ref1 and ref2 does not overlap, one value in ref1 does not exist in ref2.…
How do I calculate two aliases’ values in MySQL?
How do I calculate these two aliases and show them in other columns? SELECT SUM(qty1) AS ali_qty1, (SELECT SUM(qty2) FROM table2 WHERE col2 = 'B') AS ali_qty2, (SELECT SUM(ali_qty1) - SUM(ali_qty2) as total_qty) FROM table1 WHERE col1= 'A' table1 ID…
How can I store intervals with the same value in PostgreSQL?
I have a table: Status Date ok 2023-05-01 00:00:00 ok 2023-05-01 00:01:00 ok 2023-05-01 00:02:00 no 2023-05-01 00:03:00 no 2023-05-01 00:04:00 ok 2023-05-01 00:04:00 ok 2023-05-01 00:05:00 I want to stash statuses coming having the same value and coming in…
Postgresql – SQL: how to find value’s position in row?
I'm working with a Postgres table named orders that looks like this: user_id product order_date 1 pants 7/1/2022 2 shirt 6/1/2022 1 socks 3/17/2023 3 pants 2/17/2023 4 shirt 3/13/2023 2 pants 8/15/2022 1 hat 4/15/2022 5 hat 3/14/2023 2…