i have this SQL select SELECT t.debtor_account, COUNT(t.debtor_account) FROM transaction t GROUP BY t.debtor_account HAVING COUNT(t.debtor_account) > 2; which work, but i need select all from …
Tag: postgresql
How to exclude rows with similar columns?
There is a table: | date | action | issue_id | | 2020-12-14 | close | 1 | | 2020-12-15 | close | 1 | | 2020-12-16 | close | 1 | | 2020-12-14 | close | 2 | …
Handling of generate_series() in queries with date or timestamp with / without time zone
I have a query to generate a report based on a date series that is grouped by date and employee_id. The date should be based on a particular time zone, in this case ‘Asia/Kuala_Lumpur’. But this can change depending on where the user’s time zone is. Definition and sample data for table attendance: Here is a fiddle to test. The
Obtaining summary result since last occurence of specifc hour of day in postgresql
I have rainfall measurements stored in a postgresql table and wish to select the min and and max value since the last occurence of 9am, whether that is 9am of the current day, if after 9am, or 9am of …
Add information from two tables to second table
I’m a newbie in query building. I created two tables: product: productid | name | price order: id | productid | quantity | fullprice Can I automatically calculate fullprice (product.price * order….
Select max date per year
I have the table as follows: And I’d like to select the max date for every year per user, so the result would be like: Some help? Thank you! Answer Just use aggregation: If you have more columns that you want, then use distinct on:
How to sort and group data in request?
I have got table. It have some duplicates in purchaseNumber. I need select from this table only newest records for processing. In other words I need to: group elements by purchaseNumber sort them by docPublishDate and select newest. if any of elements in group is have status parsing_status true (suppose it was newest from step 2) any elements SHOULD NOT
Postgres comparing strings from json and table
I have the following postgres stored function: CREATE OR REPLACE FUNCTION wg.get_user( req jsonb) RETURNS jsonb LANGUAGE ‘plpgsql’ COST 100 VOLATILE AS $BODY$ DECLARE …
Upsert query is not updating the the records in postgres
I am trying to Upsert into the target table from temp table but My upsert query is not updating the records in postgresql. Although it is inserting but not updating. Please find below query: Insert …
Rails: How to sort models by an associated model’s field with condition?
I have three models: This is what I want: Feedback model has a result field. If result is true or false, the feedback is closed. If result is nil, the feedback is unclosed. I want to sort from the unit with the highest number of unclosed feedbacks to the unit with the lowest. It should be at the bottom of