Tag: postgresql

Postgres aggregrate function for calculating vector average of wind speed (vector magnitude) and wind direction (vector direction)

I have a table with two columns wind_speed and wind_direction. I want to have a custom aggregrate function that would return average wind_speed and wind_direction. wind_speed and wind_direction in …

SQL Join : how to show the first table data even the 2nd table condition false

left-join postgresql sql

I want to keep show my first table data even the second table condition is false. Table 1 ————————— ID | Name ————————— 1 | Panda 2 | Tiger ————…

Postgres SQL select query syntax

postgresql sql

I have this SQL statement, I am getting a syntax error: SELECT * FROM user_table INNER JOIN ( klass_table LEFT JOIN room_table ) Syntax error is vague, it says: You have an error …

What’s the best way to count the number of references based on conditions in both “home” and “away” tables?

knex.js postgresql sql

I’m working on a project, and using Knex for it, (though if you give me raw SQL I’m pretty sure I can convert that as well.) So, here’s the sitch. I have tables ‘group’ and ‘user_group’. table ‘…

Postgresql: Unique constraint over Union of 2 columns

postgresql sql

What is the best way to compare a name field with a string value?

postgresql sql sql-like string where-clause

I have a query that compares a string value against a name field in my database. The structure of the name field in the DB is not consistent, it could be any of these: John Doe Doe John Doe, John My …

Database Query to generate a Time-based Chart

charts postgresql sql

I have a logins table in the following (simplified) structure: Now I want to generate chart like this: https://prnt.sc/mifz6y Basically I want to show the logins within the past 48 hours. My current query: This works as long as there are entries for every hour. However, if in some hour there were no logins, t…

SQL refer Primary Key in same Table and Query

database mysql postgresql sql sqlite

I am maintaining a table of Items (Id is Primary Key). Some Items have Parent Item ( Which is also an Item, the colum Parent holds the Id of the parent Item, which is also in the same table. Some …

Count as duplicates similar values

postgresql select sql

I have following tables: Orders order_id 9 10 11 Order_details order_id, product_id 9, 7 10, 5 10, 6 11, 6 11, …