I have two tables. Let’s say they are: I’d like to make a join to get customer name, number of orders which he/she has finished and sum of money which he/she has spent in total So I’m making this SQL query: If a customer has any finished orders then it will get everything right. But if there…
SQL – Merge two rows with similar values
I have the following table called areas in a PostgreSQL server: So basically it’s nearly duplicate entries that have the same name and code, but different id, shape and created timestamp. What I want is to merge those duplicate rows, but overwrite the “shape” and “created” fields…
SQL Server : calculate column value based on value in previous row/id
I have what I hope is a simple problem to solve, but one that is puzzling me. My table can be created with the below sql: Which looks like this: This is basically a currency conversion task (the costs in the es-mx row are arbitrary in this example). What I wish to do is update all the TotalProjectCost values …
MariaDb InnoDB deadlock while doing many inserts
I’m having deadlock issues with a MariaDB (10.0.27)if it’s under pressure. The database schema is basically a hierarchy and the leaf node causes this: This is how my leaf table looks like: I’m able to reproduce the deadlock with an input set that puts the system under pressure. With this, I&…
Joining back on the same table in SQL server
I have a table like below And the output I require is The match is defined on the basis of PO number. If PO number exists in both the sources then it should be match else only in AAP and only in SNW respectively. Can this be achieved? Thanks, Ani Answer or
Nonclustered indexes will partially cover the select query
I having a non-clustered indexes created in my SQL database server with Include keyword. Please find my non-clustered index created for FACTORS table. if am having a SELECT query like below will my indcxes FACTORS_BKEY_PNO_IDX cover my select query. ( i mean will this index will help my below select query to …
Excel SQL VBA: adding more than one cmd parameter
I am writing SQL-query, and I get error with string value: I tried to add more than one cmd parameter to solve this problem: But I also get an error. How do I set more than one cmd parameter and use it in my query? Table I am working with: here. Answer The parameter to CreateParameter are The Direction –…
Will I need to do a join of 4 tables to fulfill this SQL query or is there a simpler method?
I currently need to do the following: Find the names of all course and the students enrolled on them which take place on a Friday afternoon and have at least 2 students enrolled on it. I am thinking what I should do is Join the tables titled student, takes, course, section and time_slot together and from ther…
Stuck to select maximum row
I have a table with columns: I need to select row with maximum value, and show info like I tried using group function MAX(), but I can’t select fullname, because if I use it as a GROUP BY expression, it will select max in groups. Other way, is to use WITH statement, order table by value desc, use functi…
Error – Select statements included within a function cannot return data to a client
I am trying to create a function that will query times in a table and then return the correct time based on the site. The times in the table being queried have the opening and closing times of the branch. I am not sure if I should be using CASE or IF. When I use CASE, I get this error: