I have a table like this and I would like to divide the frequency by the sum of frequency grouped by the source. Therefore, I am looking for a table like Is there any way to do this in a single sql query? Answer You can use SUM() window function. If you want a numerical result: Depending on your database,
Tag: postgresql
Do PostgreSQL server side cursors store the results to disk?
Do PostgreSQL (server-side) cursors store/materialise the entire results set to disk? Context: running some queries with a large number of results and getting disk space errors from the server. Answer Not specifically. But various operations (large sorts, large multi-batch hash joins, set-returning functions,…
Exclude ALL rows where the ids are the same, BUT one of the rows with the same ids meets the criteria and other doesn’t
How do I filter out ALL rows where the ids are the same, BUT one of the rows with the same ids meets the criteria and other doesn’t? Current Code: Answer You can try to use aggregate window function, writing your condition which you want to exclude. then filter them. Query 1: Results: If your column id_…
JOIN on two tables
I have two tables: user: and credential: I want to perform a query to get the following result (one row for every user): Right now I am executing this query: This results in: I want a single row for a user. How can I achieve this? Answer You can use two joins:
Populate all rows of a column with same value
The issue is that I have a PostgreSQL table ‘currency’ with 4 columns: ‘id’, ‘symbol’, ‘name’, ‘quote_asset’. The table contains 225 rows and column ‘quote_asset’ has all the values set to ‘null’ for now (it wasn’t popul…
Convert CTE Query into normal Query
I want to convert my @PostgreSQL, CTE Query, into Normal Query because the cte function is mainly used in data warehouse SQL and not efficient for Postgres production DBS. So, need help in converting …
SQL : Get the duration between start DateTime and End DateTime from Table entries
I have a SQL table which has entries of machine start and end time (DateTime) with duration of machine running. Given a start DateTime and End DateTime I want to calculate the duration for which machine was running. DB is SQL and Code is in C#. Note: THere might be some duration when the machine is not runnin…
Select distinct values by key from JSONB column
Postgres v12.0 I have a table data {“a”: “1”, “b”: “1”} {“a”: “2”, “b”: “1”} And I’d like to retrieve a distinct list of keys and the set of values for each key key values a [ “1”, “2”…
SQL query to find gaps within a column of dates
I have a table with status and date for every day and I’m trying to find out when the statuses change and if there’s gaps within each status change / how many days were of a certain status. Expected output: Answer This is a type of gaps-and-islands problem. In this case, subtracting a sequential n…
How to check how many issues are opened daily in SQL
I have data from issues in a table with the following schema. I want to know how many issues are open every day using PostgreSQL. We consider an issue being open at date x if: x >= created_at x <= deleted_at There may be days where no issues were created or deleted, as in the example. How can I do