How to get the absolute difference between the median house price for tat month in that municipality and the median house price for the next month in that municipality? I have a table with house data. it contains the price and municipality for each house and date it got sold. I want to get the difference of t…
How to query all rows where a given column matches at least all the values in a given array with PostgreSQL?
The request below: returns a list like this: How to get the ids for which id must have at least a and b, and more generally the content of a given array ? From the example above, I would get: Answer For two values, you can use windowing boolean aggregation: A more generic approach uses array aggregation:
How to get previous rows date in SQL?
I have a table that stores the effective date column in order. In some cases as mentioned below I effective date is NULL so in that case I am supposed to pick date from previous column and add 1 day and show as effective date. It works fine when there is a date in previous row but when 2 or
How can I calculate the median of all rows?
What I’m trying to accomplish: I’d like to calculate the median number of rows for the last 49 days for each store and hour of the day (0 – 24 hours). What I’ve done so far: My current SQL is below. I’…
Subtract values from one column based on values in another (TSQL)
I have table where I need to subtract values in one column based on conditions applied to the other. For example, I want to subtract values with code fst and trd, meaning (12 – 23). I don’t want to declare separate variables. Is it possible to make this with a query? Answer One method is condition…
SQL IN in WHERE. How smart is the optimizer?
I have the following query to execute: UPDATE scenario_group SET project_id = @projectId WHERE scenario_group_id = @scenarioGroupId AND @projectId IN (SELECT …
How to prevent MySql Injection in dynamic where?
I have an API in my C# WEBApi project which return some items from my MySQL DB. The data to return has a visibility set in a table and the params set in the API call says which kind of data should be …
How to use conditions with a RANK statement
The following piece of code does its job : it gives me the top 10 results for each category. SELECT * FROM ( SELECT *, RANK() OVER (PARTITION BY “pera_id” ORDER BY “surface” DESC) …
SQL Complex update query filter distinct values only
I have 3 tables with following columns. Table: A with column: newColumnTyp1, typ2 Table: B with column: typ2, tableC_id_fk Table: C with column: id, typ1 I wanted to update values in A.newColumnTyp1 …
group by date and two different times in the same date in Sql
i want to select from my database the data that is BETWEEN to date and two different times in the same date but when I use the Sum in one of the variants it give me a wrong result i thnik that the …