There is users table and places table. The users table has column id(primary key), username, place_count. The places table has column id(primary key), name, user_id (user_id foreign key) Each user can post multiple photos and so I want the column”place_count” to keep the count of user-specific pla…
Tag: postgresql
Calculate percentage of students SQL
How to calculate percentage of students with higher mark than average for each course? Assume I have a table (avg_marks) with average marks for each course and number of students in the course: I also have a table (enrolments) with all courses, students enrolled in those courses and their mark for the course:…
Perform a particular ILIKE query on Postgresql
I have a problem with a query in Postgres. I have a column with values such as: id@@@Name1@@@other text@@@other_text@@@other_text@@@CODE@@@other_text### id@@@Name2@@@other text@@@other_text@@@other_text@@@CODE@@@other_text### id@@@Name3@@@other text@@@other_text@@@other_text@@@CODE@@@other_text### Each row ca…
Order By two columns but have one order
This question is sequelize specific but I’d figure if I found a way to do in SQL then I could find a way to do it in sequelize I need for the request to be ordered back by user.fullname which is joined by using include and by mockUser.fullname which is a JSON field. One important thing to keep in mind
Count distinct number of customers per fiscal year and display all dates in query result
DB-Fiddle Expected Result: In the above result I want to list all order dates and count the number of customers distinct per fiscal year. The fiscal year starts two months after the calender year and therefore goes from March to February. (e.g. from 2020-03 til 2021-02). For example Customer_01 appears the fi…
Store select query as variable and loop for entire variable
I want to store the result of select query from another table and set it as variable. Then i want to loop through the variable and use it in my loop for. The question is how i can store the result of select query from my table? Answer Why not run a single query? The . . . are for
How to drop duplicate rows from postgresql sql table
I have the following dataset in a postgresql table called testtable in testdb. I have accidentally copied over the database and duplicated rows. How can I delete the duplicates? Row 1 and row 5 are copies in this frame and row 2 and row 4 are copies too. I have never used sql before to drop duplicates I have …
Update SQL database registers based on JSON
I have a table with 30k clients, with the ClientID as primary key. I’m getting data from API calls and inserting them into the table using python. I’d like to find a way to insert rows with new clients and, if the ClientID that comes with the API call already exists in the table, update the existi…
SQL Find list of tuples that fulfil at least one of the conditions
Question: Find the restaurant A that fulfils at least one of the following conditions: Conditions Restaurant A that is situated in ‘Central’ area or Restaurant A that sells at least 10 pizzas or Price of every pizza sold by A is at most $20 Expected outcomes: List with (restaurant name) Database S…
Postgres – how to displays data for the previous year?
I have sql select name, tanggal, status from tbl_person where status = ‘PROSES’ and date_part(‘year’, tanggal) = 2021 – INTERVAL ‘1 YEAR’ tanggal is date 2021-01-01 I want to …