So I’m asked to do this query for a college project: SELECT l.city AS name, AVG((pr.ap_price::double precision * 7 – pr.weekly::double precision) …
Tag: postgresql
GROUPING multiple LIKE string
Data: In above data, there are 3 kinds of strings I am interested in warning, error and timeout Can we have a single query where it will group by string and give the count of occurrences as below Output: I know I can write separate queries to find count individually. But interested in a single query Thanks Answer You can
Getting error while tryng to add geometry (polygon z) to table
I’m trying to insert a row to postgres (postgis) table and getting error. My table (geo_table) properties: I’m trying to add data: I’m getting the following error: syntax error at or near “Z” …(name, geometry, in_use) VALUES (‘tb2’, POLYGON Z (( what is the error ? and how can I add data ? Answer Try to include between single quotes
How order by case and order too?
i have a query for my next table This could be the sql query : but now i need order by case: example: when ‘Scholar’ then 1 desc its possible do that? my goal is order by id, and by case type example: the result will be: Answer Your question suggests that want a new direction within each group: However,
Remove duplicate values by taking latest data load
I’m working with enterprise data that looks like this. The issue is that the company has bad data practices and changes/reuses IDs, but only updates the load_number field. How do I construct my sql query to pull the latest loaded data like so: Basically every field will match except the id and the load_number. So given that every field matches
How to deal with fixed max number of posts a user can make within a time period?
Sorry for titlegore. Users can have at most 3 active posts, and each post is active for 48 hours. I was thinking about just putting active_post_{1/2/3}_id and active_post_{1/2/3}_expires_at on the users table, but wondering if there is a better way to handle something like this. Answer I would store only the timestamp of the post and use middle-tier logic to
Obtain latest record for a given second Postgres
I have data with millisecond precision timestamp. I want to only filter for the most recent timestamp within a given second. Ie. records (2020-07-13 5:05.38.009, event1), (2020-07-13 5:05.38.012, event2) should only retrieve the latter. I’ve tried the following: But then I’m asked to group by event as well and I see all the data (as if no group by was
How to find maximum and value of column with the same column in postgres?
I want to find maximum value of the has_sold column in an specific province.For example in all rows with province = Tehran which row has the most has_sold value? This is my table: I had tried this SELECT DISTINCT city, max(has_sold) FROM fp_stores_data_test GROUP BY city, but I’m not sure it’s right. Answer You can try the below one using
Monitor the activity of a table (number of select on a specific row)
I have a large dataset Table: id | info1 | info2 | … There are multiples process accessing the data heavily. Is there a built-in way in postgresql or an extension to know the number of time a row is accessed with SELECT ? I was thinking of making a stored procedure and manually manage it. Answer No, there is
Count days between dates based on multiple conditions
I have a table which has dates, IDs and states. Inside the postgres function I need to enter start_date and end_date. Now based on these entered dates, the count of the usage and available days should …