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, etc.) will spill to disk on their own. I don’t think server side cursors will cause that
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_code stored number value,
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 populated). Now I need to populate all the rows with the same value ‘USDT’. I tried the following query: It throws the following error:
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 running in between
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” ] b [ “1” ] Not sure how to formulate a query to achieve those results. Answer here is one way:
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 number from each day is probably
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