I am trying to get the weighted average of items from this example table: Item Value Item1 0.10 Item2 0.15 I followed this example here how to calculate it: https://www.wikihow.com/Calculate-Weighted-Average And tried this statement: I am trying to get the average by doing SUM(numberXWeightingFactor) but it d…
Tag: postgresql
how to select the max of COUNT(*) (SQL postgresql)
I switched from NOSQL to SQL and i can’t find how to select the max of count(*) i created users table, posts and comments. i want to select TOP 10 users with the max of posts and max of comments Answer First, using SQL database require you to define the JOIN statement between the associated tables to ge…
How to get columns which are not in GROUP BY?
I have a Postgresql database where I have these two tables. shipping_method shipping_details: id shipping_method_id estimated_time_min estimated_time_max price 2 1 02:00:00 04:00:00 230 3 2 00:03:00 01:00:00 500 4 1 02:00:00 04:00:00 1230 5 1 02:00:00 04:00:00 850 6 2 01:00:00 02:00:00 1785 My goal is to fetc…
In Postgresql, how do I use joins with multiple conditions including >= and <=
I have table A and table B. Each row in table A represents every time a user sends a message. Each row in table B represents every time a user buys a gift. Goal: for each time a user sends a message, calculate how many gifts they’ve purchased within 7 days before the timestamp they sent the message. Som…
Select ALL instead of ANY in a Many-to-Many relationship
Currently we have the following sample DB structure with a Many-to-Many relationship between lead and tag tables. Let’s imagine that we would like to perform the following queries. Give me all the leads that have ANY of the tags in a list. Give me all the leads that have ALL of the tags in the list For …
How does one get the total rows for a partition in postgresql
I’m using a windows function to help me pagination through a list of records in the database. For example I have a list of dogs and they all have a breed associated with them. I want to show 10 dogs from each breed to my users. So that would be That will give me ~ten dogs from each breed.. What
SQL join two tables by modifying on columns
I have 3 tables on PostgreSQL: SPENDINGS: STORE: CUSTOMER: I would love to join these tables like this: How can I do that could you help me out please? Answer trim(store.zip_code) is not good for the job because the whitespace is within the zip code text. Use replace(store.zip_code, ‘ ‘, ”) …
The native sql query to Postgresql via the @query annotation is not executed
To get the necessary data, I form an SQL query to PostgreSQL, which is a related set of View tables : When I execute these queries from the console in the database sequentially, one after the other, everything works. However, if you run similar queries in the repository via the @query annotation (“̷…
Get movies that have categories
I have associative table movies and their categories. How can i get movies that have category 1 and 2? Result: Answer Do GROUP BY. Use HAVING with COUNT DISTINCT to make sure both 1 and 2 are there. Just for fun, you can also do a self join: Or, use INTERSECT:
Full text search returning too many irrelevant results and causing poor performance
I’m using the full text search feature from Postgres and for the most part it works fine. I have a column in my database table called documentFts that is basically the ts_vector version of the body field, which is a text column, and that’s indexed with GIN index. Here’s my query: The diction…