Assume I have two tables: cameraNum roadNum isWorking 100 1 TRUE 101 1 FALSE 102 1 TRUE 103 3 FALSE 104 3 FALSE 105 7 TRUE 106 7 TRUE 107 7 TRUE 108 9 FALSE 109 9 FALSE 110 9…
I have the following table (stripped down for demonstration) products - id - part_number - group_id I want to be able to query against products and only return a single row per group_id (whichever is noticed first in the query…
I have a db like this [ { _id: ObjectId("1"), name: "John Doe", age: 25, address: { street: "123 Main St", city: "New York", state: "NY", zip: "10001" }, images : [ { url: "http://www.example.com/image1.jpg", caption: "Image 1" type: "jpg"…
In Postgres, we want to use the window function as an aggregate function. We have a table, where every line consists of two timestamps and a value. We first extend the table by adding a column with the difference between…
The goal is a custom aggregate function made with CREATE AGGREGATE called string_agg_oxford; it is an aggregate function that works similar to string_agg except it is smart enough to know how many items it is aggregating so that it can…
As title. I write some SQL code like below : SELECT p.id, json_agg( json_build_object('id', sp.id) ) AS subProducts FROM product p LEFT JOIN sub_product sp ON sp.product_id = p.id GROUP BY p.id as will be observed within the demo but…
I have the following two tables: CREATE TABLE products ( id INT, created_at DATE, sold_at DATE ); CREATE TABLE product_prices ( id INT, product_id INT, price numeric, created_at DATE ); The data model logic works as follows: When a new…
In Postgresql, suppose I have a 2-col table containing 3 records, namely tA id | col_A | col_B | type 1 | 1 | 2 | A 2 | 3 | 4 | B 3 | 3 | 1 |…
I have the following table in MySQL which has only two columns. First_name Last_name --------------------------- Babara MacCaffrey Ines Brushfield Freddi Boagey Ambur Roseburgh Clemmie Betchley Elka Twiddell Ilene Dowson Thacher Naseby Romola Rumgay Levy Mynett I want to extract the…
I'm trying to join a new column to my current query that uses aggregate functions. I create this column with a new query that also uses an aggregate function from a different table but I'm not sure if a JOIN…