I want to use columns from three different tables and use them to calculate how much each customer has ever spent. tblCustomer(CustomerID) tblOrder(CustomerID, ProductID, Amount) tblProduct(ProductID, Price) So I want to filter out the orders made by a customer, check what product they ordered and what amount…
Tag: mysql
Convert nested select to join
Account Table: Answer Your query is probably more efficient than a join/group by version, but it is equivalent to: I should note that if all banks have loans (which seems reasonable) no JOIN is necessary:
Sql to fetch records only if related other table records exist
Table: user id compId 1 comp1 2 comp1 Table: Company id name comp1 coke comp2 pepsi need a MYSQL query which should fetch company record only if it has one or more users, when passed a company id. I will have other where conditions on company table. Can this be achieved by joins? example 1: query(comp1) resul…
Replace “OR” on 2 indexes with a faster solution (UNION?)
I’m querying shopping-carts in a shop-system, like: I need to query records of c with a column which shows the number of carts which have the same user OR the same email. So I do: This works, but the problem is that the OR is very slow, because MySQL/MariaDB doesn’t use any key in the subquery: Ev…
MySQL merge two tables with the same IDs
In table_A I have data from this week, the IDs started from 1 up to 5000. In table_B I have data from last month, whereas the first let’s say 3000 IDs conflict with the ones in table_A, and it’s not the same data for all of the IDs (ID 1 in table_A is not the same as ID 1 in
SQL Query between a date range and a time range in MariaDB
I’m having trouble writing this query to give me any results. I’m using MariaDB as well. The DATE_ADD clause works just fine and gives results within that interval, but as soon as I add in the TIME function nothing is returned in the results. The CallDate format is 2021-09-21 HH:MM:SS I have tried…
How to create a derived attribute column in sql which is sum of other two existing columns
I want to have a column selling_price which is sum of scost and dcost. Answer You can use MySQL generated columns for it. Reference Doc
SQL: How to divide column by counts?
So I have this query This will print something like device_id paper_count 1 5000 2 10000 And I have this other other query that will return the number of times the toner has been changed according to some date. I used count to count the number of times it’s been changed. This will print something like d…
How to write a query to add a prefix to a comma separated string in MySQL?
I have a table in a MySQL database with a column id. The id column has comma separated values stored in it in VARCHAR format. I want to add a prefix C to every value in the output of the query. Ex: Answer This all sounds like a bad idea, but in some situations it might be needed. It seems
Find rows where sum of columns from multiple rows is greater than X
The best way to explain this question is through the real life example it would solve. I have two tables, Match Overview and Match Attributes Match overview looks like While match attributes looks like Where match_id is a foreign key for id in the match overview table, and attribute id is the integer represen…