How can I rewrite this?
MySQL / MariaDB structure: Person | id | |----| | 1 | | 2 | | 3 | Car | person_id | car | |-----------|-------| | 1 | Ford | | 2 | Mazda | | 3 | Ford |…
How can I print record count from 2 tables by grouping in SQL?
I need a sql query to print the count of records from different table for particular column. I have tried the below but it is not working. SELECT t1.names, COUNT(t1.name) as Table1 count, COUNT(t2.FILENAME) as Table2 count FROM Table1 t1…
Can MySQL join 3 tables & aggregate each w/o a subquery?
There are 3 tables, A, B, C. Table A has an id column, and B & C integer column which represents money and a_id which reference id in table A. Below is an example of table A, B, C table…
How do I calculate two aliases’ values in MySQL?
How do I calculate these two aliases and show them in other columns? SELECT SUM(qty1) AS ali_qty1, (SELECT SUM(qty2) FROM table2 WHERE col2 = 'B') AS ali_qty2, (SELECT SUM(ali_qty1) - SUM(ali_qty2) as total_qty) FROM table1 WHERE col1= 'A' table1 ID…
Can multiple inner joins duplicate data in Postgresql?
I have drawn a simple ERD: The query I perform: select s.*, count(c.store_id), count(p.store_id) from store s inner join customer c on c.store_id = s.id inner join product p on p.store_id = p.id group by s.id, s.store_name, s.address The result:…
Can I retrieve rows that are fully compatible with each other in a MySQL database?
I have this simple structure in my database CREATE TABLE species ( _id INTEGER PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE compatibility ( _id INTEGER PRIMARY KEY, speciesA INTEGER, speciesB INTEGER, compatibility TINYINT NOT NULL ); I want…
MySQL INNER JOIN query to get client information having at least 10 rentals
I am trying to query the code, name, and the number of rentals of customers who have made at least ten rentals. My query: Select client.ClientCode, client.ClientNom, (location.LocationRef) FROM client INNER JOIN location ON client.ClientCode = location.ClientCode WHERE location.LocationRef >…
Mysql – function sum() 2 fields with same id
I am trying to get the sum of particular field. I am getting the SUM wrong when I try to get the values together. I have this: reference: id_skill, user, level_paid More specifically the table reference as: id_skill user level_paid…
Mysql – How to find first and last value of a column in SQL?
I have a table having below schema Item:Varchar Date:Date Quantity:Float trasactionid:int Sample Data: Item Date Quantity transactionid Part1 01-01-2023 10 3 Part1 01-01-2023 15 5 Part1 01-01-2023 17 2 Part1 01-01-2023 13 6 Part1 02-01-2023 13 7 Part1 02-01-2023 1…
Using mysql, how can I relate many keys in one table to keys in another table using a single query?
I have a mysql table named 'access' with 2 fields: 'owner_id' and 'date_created_id'. I have another table named 'usersx' with 2 fields: 'id', and 'last_name'. I would like to use a single query to get both last names from the…