All Questions
123 questions
0
votes
0
answers
43
views
What edge/corner case am I missing with this MySQL query?
This leetcode sql question was giving me problems earlier today. The question is as follows:
Table: Weather
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id ...
- 43
-2
votes
1
answer
40
views
JOIN and GROUP BY DATES but missing empty rows
Maybe I'm tired, but can someone please explain to me why I'm not getting the empty rows?
I want this:
230901
id from join
id from join
id from join
230902
no rows matches in table B
230903
id from ...
- 39
0
votes
1
answer
42
views
How to Join based on whether the date RANGE of table 1 intersects with the date range of table 2
left join Mars_Crater_Names n1 on t1.ID=n1.ID and t1.StartDate between n1.StartDate and n1.EndDate
The problem is that the start date of a record in T1 may not fall between the StartDate and EndDate ...
- 5
3
votes
1
answer
94
views
How can I get daily login user number?
I have a logged_log table, the table have username, login_time and logout_time column.
I need to count daily login user.
Table structure:
column name
type
username
varchar(32)
login_time
datetime
...
- 59
0
votes
0
answers
40
views
Joining two tables produces no result despite common values
My fiddle is here
What I am trying to achieve?
I need to sum up income and sum up ad spend for each product in each month.
So the first query is summing ad cost for each product each month.
The second ...
- 29
0
votes
0
answers
28
views
Select last_update lower then date less X days from another table
I'll try to explain as better as is possible. We have a table with products, all products have a last_update datetime field. and I have a table with a list of suppliers, and i need to get each day ...
- 1
0
votes
1
answer
60
views
SQL Create Consecutive Dates Even Count is 0
i have log table that looks like below, i want to collect log table for my chart, i want to collect data based on category and date, scrool down for my sql code
id
category
date
2
penghapusan
2021-...
user9567039
1
vote
2
answers
54
views
Adding Missing Dates To Each Location - MySQL 5.7
So my SQL output is:
DUNF 2021-04-01 18
DUNF 2021-04-02 17
DUNF 2021-04-03 7
DUNF 2021-04-04 10
DUNF 2021-04-05 18
DUNF 2021-04-06 20
DUNF 2021-04-07 17
DUNF 2021-04-08 ...
- 15
0
votes
1
answer
35
views
Count nested select using join and by month
Hi I have three tables here is all
First. count_internets
id| company_id | item_id | created_at
1 | 1 | 1 | 2020-10-14 |
2 | 1 | 2 | 2020-10-15 |
3 | 2 | 4 ...
0
votes
3
answers
39
views
How to get the highest date for each register after left join in mysql?
I have two tables (user and log) in mysql.
USER TABLE
id email name status
-- ------- ------ --------
1 "[email protected]" "Carlos" 1
2 ...
- 23
2
votes
2
answers
199
views
Compare two MYSQL tables and return missing dates and group by column
I have two tables. I want to compare table A with B and get the missing date from with name of the user.
TABLE A
|----|----------|------------|
| 1 | king | 2020-08-01 |
| 2 | king | 2020-08-...
- 67
0
votes
1
answer
55
views
How to delete rows using 2 different columns as condition in a SQL query?
My table is something like this:
Name Order Goods Date
------------------------------------------------
Michael 1 Shoes 2019/04/05
Michael 2 ...
- 165
2
votes
2
answers
119
views
Unable to put dates in a column and metric in another in SQL
Question: For a given day t, I need to create an on-time delivery (OTD) metric for 61 days for seller_id "123".
The OTD is the rate at which packages were delivered on-time
for packages ...
- 57
0
votes
1
answer
24
views
Showing all customers for each date record provided after they were onboarded
Date_spine Table: It has all the dates of the month of march, 2020
DATE
2020-03-01
2020-03-02
2020-03-03
2020-03-04
2020-03-05
2020-03-06
2020-03-07
Customer table:
id | ...
- 57
0
votes
2
answers
74
views
A way to have a rolling summation
I have the below dataset. In the below example records for the year 1993. The Tgrowth column is start - end. Started is the number of employees that joined on a specific month and ended is the number ...
- 522
1
vote
1
answer
2k
views
mysql left join with group_concat - only shows a single result
So I am not sure if I am taking the right approach but here is what I am after:
I need to get all the records from table A
Then join on table B and concatenate all the values that match a specific ID ...
- 33
0
votes
2
answers
35
views
MySQL - search records from table B which lie within multiple time ranges from table A
MySQL version = 5.7.29
This is my table A:
╔════════════════════╦════════════════════╦═════════════╗
║StartDate ║EndDate ║Var ║
╠════════════════════╬═════════════════...
- 1,803
0
votes
2
answers
54
views
SUM () in a where - Mysql
The query below shows results of a contract, whose contract delay is between 1 and 487 days.
Now I want to add the values of the invoices belonging to the invoice.value contract and then filter by ...
- 405
1
vote
1
answer
184
views
how to print data from second table in MySQL
I have main table called companyNames, and each row contains the name of the company, for eg. TCS, TATAMOTORS, INFY, etc. and also having their tables, like table TCS, table INFY etc, so i'm trying to ...
0
votes
1
answer
114
views
How to use PDO::FETCH_GROUP with a table join and only returning 3 records in a joined table ordered by date
I am using the following code to get a grouped list of voyage types and their respective voyages.
public function getVoyageTypesWithTrips() {
//query
$this->db->query('
SELECT voyagetype_name
...
- 515
1
vote
1
answer
42
views
GROUP BY retrieve from the history table the last event
I have 3 tables and I want to retrieve from the history table the last event, so I wrote this request but the result is not good. or am i mistaken? thank you
CREATE TABLE `equipment` (
`...
- 23
0
votes
2
answers
49
views
MySQL nearest date without duplicated data
So I need to display all my customers and with the associated booking number (null if there is no booking) without duplicated custome. If the customer has lot of bookings I need to display only the ...
- 4,971
-1
votes
1
answer
49
views
How to SELECT row with MAX() data of column?
i have some table of database :
Transaction detail_transaction product
2 (id_sale) 1 (id_detail) 3 (id_product)
Yes (...
- 45
0
votes
2
answers
24
views
I need to get the delta between the date of first payment and user registration date by user
I have two tables: payments, users.
users:
id regtime
2271 2020-01-17 16:38
payments:
user_id datetime
2271 2020-03-20 15:11
I need to get the date of the first payment for each user ...
0
votes
3
answers
60
views
SQL NOT BETWEEN syntax in query on MySQL
On the table calendar_recovery_interval_hour_2020 I divided the days into time slots of one hour each, e.g. 2020-04-14
+---------------------+---------------------+-----+
| start_date | ...
user13281062
0
votes
2
answers
29
views
Summing condition excludes rows without positive sum
I'm having a weird issue where in returning the sum of rows where a condition is true seems to only return rows if that condition is true - or at least, it is excluding a large majority of the rows ...
- 1,394
0
votes
2
answers
429
views
Return results for all days of the month even if there is no data
I have a select that returns a data set by DATA.
Example:
As you can see in the image below, there is no data for the days: 20,21,22 ... 28
I would like to bring these lines with result NULL
My ...
- 172
2
votes
2
answers
158
views
Scoreboard from MySQL data
I have a database with two tables. One holds item data, and one holds price statistics for the items in the other table.
Item Table
itemid (int, auto increment) | name (varchar) | description (...
- 21
0
votes
1
answer
680
views
show days with 0 records in mysql query when grouping by date
I have a table of ids and dates. the table is like this:
id | Date
5 | 2020-04-01
6 | 2020-04-06
7 | 2020-04-11
8 | 2020-04-11
I want to group by date so I used this:
select Date,count(id) from ...
- 45
2
votes
1
answer
34
views
mySQL joins query with dates
hey so I have these tables and data:
create table CUSTOMER(CustNum INT, CustTell INT, CustFname VARCHAR(15), CustLname VARCHAR(15), CustCounty VARCHAR(20),
CustTown VARCHAR(20), CustStreet VARCHAR(...
- 67
0
votes
1
answer
571
views
MySQL: how to get average time spent by all users and single user
I have two tables every time when user comes I create new New user record table is following
tbl_users:
---------------------------------------
id | name | created_at
-----------------------...
- 417
0
votes
1
answer
75
views
MySQL - selecting all rooms including partial date ranges
Im currently have room availability present which displays current open rooms for bookings given a specified date range.
I need to display the same availability but instead of displaying rooms for ...
0
votes
2
answers
303
views
Arrive at start date and end date with in the same table with multiple orders across different user id's
I have two tables one with unique id and a timestamp that tells when did a user first enter a store and the other with unique id time stamp and list of orders placed by each unique id with their ranks ...
1
vote
1
answer
202
views
Search for free times in calendar with MySQL
I have a users table in which I keep user info alongside their day start and end time
id day_start day_end
1 08:00:00 16:00:00
2 09:00:00 18:00:00
and there is another ...
- 116
0
votes
2
answers
96
views
SQL query to get a joinned table
I have two tables that I need to join and need to get the data that I can use to plot.
Sample data for two tables are:
**table1**
mon_pjt month planned_hours
pjt1 01-10-2019 24
pjt2 01-...
- 181
1
vote
2
answers
44
views
Is there a way to display negative values with the case function?
Question:
Provide a report that shows the customer’s full name (first and last name together), the cottage name, the region name of the cottage, the date of booking (in the format 15th of December, ...
0
votes
1
answer
166
views
Join on Date Ranges
I've looked at other answers to this question but haven't found a solution.
I have two tables with a tracking number, one has status history and several records per tracking with different date times ...
- 1
1
vote
1
answer
60
views
mySQL Query: How to choose just one comment from post list? [duplicate]
Table post:
id
Table post_comments
id, post_id, comment, created_at
REQUEST:
SELECT * from post
left join post_comments on post_comments.post_id = post.id
This request return all of the ...
- 362
0
votes
2
answers
35
views
Artificially duplicate results in mysql
I have a table like so
+---------+
| weekday |
+---------+
| 2 |
| 5 |
| 3 |
+---------+
Now I want to have a resultset in which I see the dates of this and the upcoming week like ...
- 2,699
-1
votes
1
answer
63
views
mysql select join format from two tables [closed]
I'm trying to select data from one table based on a date range of data from another table.
First table: clients with fields firstname, lastname and telephone.
Second table: orders with a field date
...
- 3
0
votes
1
answer
24
views
Comparing Row Values Based On User MySQL
I thought every MySQL query question would be answered by now, but I have a query that I can't find a good answer to.
I would like to run a query analyzing the differences between rows in one table, ...
- 1,583
1
vote
1
answer
59
views
Create query to get count of uncompleted calls group by 2 fields
A small update of this question Create query to get count of uncompleted calls
There is table waiter_log as
+---------------------+---------+----------------+--------------+--------------+
| ...
- 1,658
1
vote
1
answer
51
views
Using MIN value after JOIN MAX(date) MYSQL
I have 3 table. manufacturers, products and prices
I want to get the last price of product and select min price of them.
Table manufacturers:
# manufacturers
id name
1 Manufacturer 1
...
- 1,267
1
vote
1
answer
52
views
Calculate in a custom column by using subdate to get the previous day
I am trying to use the subdate method in mysql but I can't get what I want...
Consider I have the below table :
Table:
id created_at value
1 2019-03-19 50
2 2016-03-20 100
3 ...
- 468
2
votes
4
answers
361
views
Working with a MariaDB view that generates a lot of calculated statistics - How to move to a calculated table?
I currently have a MariaDB database that gets populated every day with different products (around 800) and also gets the price updates for these products.
I've created a view on top of the prices/...
- 151
0
votes
2
answers
50
views
Mysql Foreach from one table to another Part 2
This has connection with my previous question
Mysql Foreach from one table to another
I managed to get the everyday logs but what I'm stuck now is how to get everyday logs for the past 5 days with ...
- 81
3
votes
1
answer
67
views
missing row if sales did not occur in one year vs other
Given below is the table definition and data:
CREATE TABLE bike
(
id INTEGER,
name VARCHAR(50),
price INTEGER
);
CREATE TABLE country
(
id INTEGER,
country VARCHAR(50)
);
create table ...
- 33
0
votes
3
answers
73
views
mysql LAST_DAY() only reads 1 subquery result, how to process all results? using joins?
I have an insurance policies table like this:
+-------------------------------------------------------------+
| id | cancellation_val | cancellation_interval | expire_date |
+-------------------------...
- 15
1
vote
1
answer
35
views
Null Condition with current month condition
i am trying to run a mysql select query where i want to fetch the students who did not pay the fee at current month. please check my condition if i use these one by one it works but they both at same ...
- 31
-1
votes
2
answers
509
views
MySQL query: How to get the trending posts ordered by latest submission date
I am trying to get the latest trending posts, which are suppose to be the post with the highest number of Views, and for that I am using the following MySQL query:
SELECT
p.id,
p.date,
...
- 51