I have a table with position (junior, senior), salary, and an ID. I have done the following to find the highest salary for each position. SELECT position, MAX(salary) FROM candidates GROUP BY position; What I am getting: How I want…
I am using sqlalchemy with postgresql, Tables Shape[id, name, timestamp, user_id]#user_id referring id column in user table User[id, name] this query - query1 = self.session.query(Shape.timestamp, Shape.name, User.id, extract('day', Shape.timestamp).label('day'), extract('hour', Shape.timestamp).label('hour'), func.count(Shape.id).label("total"), ) .join(User, User.id==Shape.user_id) .filter(Shape.timestamp.between(from_datetime, to_datetime)) .group_by(Shape.user_id) .group_by('hour') .all()…
I have a table containing two columns : Fruits & Color Fruits Color Apple Red Orange Orange grape NULL On the other hand, i have another table, with only fruits, and an empty Color column. Fruits Color banana NULL Orange…
I am trying to get the sum of multiple values of a column in a single row with LEFT JOIN. Here are the tables CREATE TABLE stocks ( id text NOT NULL, quantity bigint NOT NULL DEFAULT 1 ); CREATE…
I've got the following tables: person (id), person_agency (person_id, agency_id) and agency(id, type) this is my query: select p.id, a.id from person p left join person_agency pa on p.id = pa.person_id left join agency a on pa.agency_id = a.id where…
I want to create an enum column in a Supabase table. Supabase uses Postgres under the hood, so I understand that technically it is possible if I do it manually using SQL. But is there any way I can do…
I've got a table to store collected data from several energy meters, then I created some views to show data from specific meters only. Now I want to combine those views for an overview of only interesting data. As far…
We are structuring a project where some tables will have many records, and we intend to use 4 numeric foreign keys and 1 numeric primary, our assumption is that if we create an index for each foreign key and the…
CREATE TABLE Log ( "Name" TEXT, "Age" TEXT, "Country" TEXT, "Numbers" TEXT ); SELECT "Country", "Numbers" FROM json_populate_record( null:: log, '{ "Name": "qazwsx", "HostName": "Age", "Address": { "Country": "MNB", "Numbers": [ { "Cell": 7418520 } ] } }'); SELECT *…
Let's say I have a table in Postgres that stores a column of strings like this. animal cat/dog/bird dog/lion bird/dog dog/cat cat/bird What I want to do, is calculate how "correlated" any two animals are to each other in this…