Mysql – SQL indexing with 2 join tables
I have 3 tables. country, team, player table Country id name 1 x 2 x 3 x table Team id name country_id 1 x 1 2 x 1 3 x 1 table Player id name country_id team_id 1 x 1…
MySQL get all weekend days between two dates
I'm trying to calculate how many weekend days passed between two dates. SELECT DISTINCT start_date AS fr_date, end_date AS to_date, DATE_FORMAT(start_date, '%a') AS wd_fr, DATE_FORMAT(end_date, '%a') AS wd_to, ( FLOOR(TIMESTAMPDIFF(DAY, start_date, end_date) / 7) * 2 + IF(DATE_FORMAT(start_date, '%w') IN…
Mysql – How to calculate stock with price using FIFO method
I would like to ask how to use the FIFO method with MySQL 8 to generate the expected result as follow. dbfiddle product_id type quantity price operation_date 1 PURCHASE 10 100 2024-08-01 2 PURCHASE 25 80 2024-08-02 2 SALE 20…
MySQL Exclude values
I have two tables: TESTS: ID YEAR DESCRIPTION 0 2023 some text .. ... ... N 2024 some text test_prep_materials: ID PATH TID 0 /home/user/file0.pdf 0 .. ... ... M /home/user/fileM.pdf 0 So any test have prep materials. And some…
Mysql – Getting "Error converting data type varchar to numeric."
Basically I'm trying to insert 5 rows of data into my Book Table and it gives me an error saying that it can't convert the data type varchar to numeric. This is the query I was entering INSERT INTO Book…
Mysql – print only the student names who have performed better in their respective latest years
there is a table student|Year|Marks ABC|2023|98 XYZ|2021|100 MNO|2019|81 XYZ|2020|98 MNO|2018|98 ABC|2022|100 the result should just show XYZ|2021|100 i am trying to create the query as with cte as (select *, row_number() over (partition by name, year order by marks) as…
Mysql – How to show name instead of ID in same table multiple times with empty values?
I have 2 tables. Table 1: ID, ReceiveID, ProcessID, DeliverID 1 1 1 2 1 2 3 3 3 Table 2: ID, Name 1 Jack 2 Mary 3 Paul I want to get: Table1ID, Receiver, Processor, Delivered 1 Jack Jack…
MySQL How to Dynamically Select Columns which have at least a value
In MySQL, I have a table which consists of 300+ columns, I have to extract only columns which does have a value at least in one row (not null). Some answers from other similar question suggested that we can use…
How do I fill in missing data when importing json data from Python to MySQL
I am rather new to all of this but I am trying use python to grab API data from a url and push it into a MySQL database. The problem I am running into is that some data is missing…
Mysql – How do I get a COUNT of rows using UTC grouped by date – but in the end-user's timezone?
I need to write an efficient query from my backend application so that I may know how many online sessions are there (count) per day for a given date range. Sample input: Year: 2020, month: 08, timezone: "Asia/Dhaka". <- timezone…