I’m new to SQL and I am trying to write a query on PostgreSQL to find the cust_ID and customer name of each customer at the bank who only has a loan at the bank, and no account. The database schema is: The results should have only these 3 customers: My two attempts for this query are: 1) Where my
Tag: postgresql
GROUP on one column and aggregate counts based on distinct values in another column
I have a table with values like so: I want to do a GROUP BY based on ts and display the counts for distinct values of usr_id like below. ts count of A count of B count of others 11 2 1 3 22 0 1 0 33 1 0 3 http://www.sqlfiddle.com/#!9/bbf4fc/2 I couldn’t make much progress beyond doing the
sql: exclude rows if at least one row within it isn’t in the list
I have the following simple table COL1 COL2 COL3 U1 L1 X U1 L5 X U2 L2 X U3 L2 X U4 L4 X U4 L6 X U5 L7 X when I execute the statement Result: My desired result is The U1 and U4 should be filtered out because one of their COL2 contains an element which is not in
Sum of column values inside Json array with group by
I have next Django model. I store JSON data in wh_data. Note: it’s data for one row – 2022-06-06. I need to calculate the sum inside stocks by grouping them by wh and by created_at so that the output is something like this I know how to group by date, but I don’t understand how to proceed wi…
SQL: partition by / window function with the combination of 2 columns, separated by todays date
I am trying to create a materialized-view in postgres, where I sum 2 columns, depending on todays date within a window function. In the below picture there is an example of the query I am trying to achieve: If today is the ‘2022-06-06’: 2022-06-05: 1+2+3+4+5 2022-06-06: 1+2+3+4+5+6+107 2022-06-07:…
Query using CASE in left outer join takes long time to run
I am running a Postgres query with a CASE expression in a join condition. The query takes a long time to run. Is there a better way to optimize this query? Code snippet: Answer For proper answer attach full query, table structure (with indexes) and execution plan. Original CASE is quite complicated, but hard …
Pivot by Country Name and Flag in Postgresql
and I am trying to cross tab it like this: Here is my SQL query, I am not able to do it since I do not have permissions to create extensions (tablefunc) Is there an alternate way? Can you please help? Thank you. Answer As you ave only 2 values, you can make the classical approach to pivot country |
How to Count Entries on Same Day and Sum Amount based on the Count?
I am attempting to produce Table2 below – which essentially counts the rows that have the same day and adds up the “amount” column for the rows that are on the same day. I found a solution online that can count entries from the same day, which works: It is partially what I am looking for, bu…
change the type of column in postgres not working
I have a column of type object, and i need to make it from another object type, (the table is empty ) i tried to do : ERROR: function pg_catalog.btrim(invoice_item) does not exist LINE 1: …ory ALTER COLUMN invoice_info TYPE invoice USING (trim(invoi… ^ HINT: No function matches the given name and …
How to expand date ranges by taking into account overlapping days in PostgreSQL?
Given the following table created in a PostgreSQL database using the following SQL statements: The table represents the list of reservations of a Bed&Breakfast with check-in date and check-out date. It is requested to retrieve how many rooms should be set up and available for each week. In order to reduce…