All Questions
167 questions
0
votes
2
answers
60
views
How to query from mutiple tables and group them by DATE in laravel?
I want to get data from two tables and group them by date, like a daily report that shows data from two tables.
Example output:
Date
Active User
Total Deposit
2023-07-31
10
12500
2023-08-01
5
10000
...
- 23
1
vote
1
answer
11k
views
Generate 1 row for every month between a range of dates
I have the following data, as an example:
Name | Value | startdate | enddate
1 | 10 | 2023-04-01 | 2023-06-30
2 | 99 | 2023-03-01 | 2023-05-01
I need to get the ...
0
votes
0
answers
264
views
MySQL cumulative sum with multiple group
I saw correlative posts about this problem but I cound't find the solution for my problem.
I'm trying to add a column with cumulative sum of another column but with multiple parameter for grouping.
...
- 19
1
vote
2
answers
34
views
Need MYSQL database query solution
In MySQL I have the following table:
date
work
code
2022-01-01 11:41:24
10
1
2022-01-01 10:41:24
10
1
2022-01-03 09:41:24
0
0
2022-02-04 06:41:24
10
1
2022-02-05 05:41:24
40
1
My SQL code:
...
user17635247
0
votes
2
answers
909
views
How to SELECT customers with orders before specific date
I have two table.
Table 'customers': customer_id, date_register, name
Table 'orders': order_id, customer_id, order_date
Now I want the customers who have orders before specific date and have NOT ...
- 423
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
0
votes
1
answer
42
views
How to make a COUNT of items that can appear in two different tables with a GROUP BY per day?
I have a SQL query that tells me how many people have logged in at least once in the past day and I would like to make a query to know how many people have logged in at least once or read an email in ...
- 3,276
0
votes
1
answer
21
views
How to retrieve the last updated information in each row
I have the table that is shown down below and have to retrieve the last updated code of the num, which I know by the date, but I don't really know how to retrieve that..
num
code
created
778951
...
- 5
-5
votes
2
answers
670
views
Get data in current date last hour in sql
i want to count the data in current date last hour (for example now() is 2020-04-11 12:30:00 , then i want to get data before 12. any idea ?
table_a
timestamp
2020-04-11 10:00:00
2020-...
- 111
-2
votes
2
answers
60
views
Group by month with date time in sql
halo i want to count the data and group by month (month start date) then show in date time, any idea ?
table_a
timestamp
2020-11-28 04:00:00
2020-11-28 05:00:00
2020-12-29 01:00:00
2020-12-29 02:00:...
- 111
0
votes
1
answer
24
views
MySQL query to count occurrences on days of the year
I have a (large) table like this:
id venue city date time
1 Waldorf Hotel London 2020-01-01 07:00
2 Waldorf Hotel London 2020-01-02 07:00
3 ...
- 3
-3
votes
4
answers
470
views
How to Calculate Cumulative paying MRR using SQL?
I have a table which contains the following data:
Customer_ID | Date | MRR_Change
MRR_Change is the incremental revenue change in the customer's subscription over and above the previous transaction. ...
- 13
0
votes
2
answers
258
views
SQL Date range by month returns in multiple rows
I am looking to get the count of entries by the month. However, this is returning the total count.
select count(*) from TBL_NAME
where (dateAdded between '2020-01-01 00:00:00' and '2020-01-31 23:...
- 2,896
-1
votes
2
answers
31
views
mysql get result from join table
I have a tables person and exam.
person
-------------
id | name
-------------
1 | J.Doe
2 | Snow
exam
----------------------------------------------------
id | person_id | exam_no | ...
- 65
0
votes
1
answer
49
views
Calculate average, add date, and save query to a new table
I have a table named pt_products with the fields:
`id` int(6) unsigned NOT NULL,
`ean` bigint(13) unsigned NOT NULL,
`merchant` varchar(64) NOT NULL,
`price` decimal(10,2)NOT NULL,
PRIMARY ...
- 103
0
votes
1
answer
28
views
How to Group result from mySQL
My Table:
datetime. employment. name
2019-11-25 12:32:12. office. Michael Jackson
2020-01-31 12:32:22. production. Jenny Darling
2019-12-25 12:32:12. ...
- 3,998
1
vote
1
answer
53
views
How do i join a table to itself using an incremental loop using a variable that is set in a table row?
How do i join a table to itself using an incremental loop using a variable that is set in a table row?
The closest I can get is below and but:
SELECT A.post_id, A.meta_value AS From_Date, B....
- 2,635
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
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 ...
1
vote
1
answer
23
views
mySQLi --> Is it possible to create a query that would calculate the sum between dates
I have a mySQL table that contains at least the following field:
ID | user | date | balance
I would like to know if it is possible to sum the column balance group by user for dates that are between ...
- 13
2
votes
1
answer
56
views
SQL query to find correct pairs of engine running
I got data in an existing table with events like active (a) and inactive (i). Its something like logging if a component is active or inactive. Due to an old interface, there are no correct pairs of ...
- 23
2
votes
2
answers
2k
views
How to group results by a date/time period in MySQL while still using an index?
In MySQL, you can create an index to be used in a query to prevent full tables scans. Only one index can be used.
Furthermore, in order to use the index, the field(s) indexed cannot be run through a ...
- 57.1k
0
votes
1
answer
164
views
Select This Year and Previous Year mysql on Same Row
I'm struggling to select the COUNT of 2 fields grouped with this_year_total and last_year as the total for date - 1 year. I've tried with no solution working so far
This is once method tried, but may ...
-1
votes
1
answer
60
views
Getting the list of all months and the number of products ordered for the month, and should have '0'for the month with no orders - mysql
I have a set of data that looks like this
Cust Id order date Ordered Product
1 Jan 2 1
1 Jan 5 2
1 March 14 1
1 September 9 1
1 ...
- 19
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
1
vote
2
answers
1k
views
How to calculate Day on Day % Change in Revenue in MySQL?
I have a table revenue which contains
Date_Field | Country | Category | Revenue | Buyers
I want to calculate Day on Day percentage increase in revenue. I have written the below code and am getting ...
- 13
0
votes
1
answer
48
views
How to pivot in mysql for previous date in mysql
The table structure and data is present at https://www.db-fiddle.com/f/5rKXiavsoMeQazSHwxaTVK/0
i am trying to get the percentage of all previous date range.
circle_name`|current_capacity|2020-03-16|...
1
vote
1
answer
62
views
Calculate open cases per day
I am working on queries which are based on time and I would like to get the optimal way to calculate opened cases during the day. I do have table task_interval which has 2 columns start and end.
JSON ...
- 1,436
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
-1
votes
2
answers
84
views
Finding response time grouped by intervals
I have a table where I have certain logs and their response time. I can query the table to get the average response time over the last 1000 records by doing:
SELECT timestamp, count(*), avg(...
- 4,717
0
votes
1
answer
590
views
UNIX Timestamp Time Difference Average MYSQL
I am currently working on a ticket system in which I would like to work out the average amount of time it is taking staff to respond to tickets.
I have 2 columns that hold the UNIX timestamps: ...
- 53
1
vote
1
answer
47
views
is it possible to use more than 1 query in mysql?
hi guys i have a doubt here, i have my website and im trying to echo in php from my database the max temperature, minimum temperature, most recent temperature that entered the DB and i´ve looked ...
0
votes
1
answer
124
views
mySQL– list how many films had been directed?
I just have got an exercise from school, where I have to display how many films the person in the task directed each year. How can I do that with mySQL? (I have already tried to pair the same year ...
user13315598
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
2
votes
1
answer
307
views
Invalid use of group function in Lag Function
I am brand new to SQL and have a fairly simple query but I keep getting the error "Invalid use of group function" when trying to use it. Here is my query:
select CreateDate as date,
count(*) as ...
- 2,227
0
votes
1
answer
977
views
SQL query for finding maximum amount month wise with the user [duplicate]
I want to print maximum sum amount per month with the user who has done the maximum amount. My table structure is
+--------------+-------------+------+-----+---------+-------+
| Field | Type ...
-1
votes
1
answer
86
views
SQL how can we get monthly trend from 2 separated columns start_date and end_date?
Imagine we have the following data:
ID,State,start_date,end_date,Product
S1,Trial,2020/01/01,2020/01/07,Hulu
S1,Paid,2020/01/08,2020/01/31,Hulu
S1,Expired,2020/02/01,null,Hulu
S1,Paid,2020/03/01,...
- 3,135
0
votes
2
answers
71
views
Mysql query per date and per group
I have an SQL query which counts user uptake (registrations) by day for a particular group of users. It also does a cumulative (running total)
select Date, Cumulative, Up as Uptake
from (
select ...
- 3,622
1
vote
3
answers
753
views
How do I select SQL data in buckets when data doesn't exist for one bucket?
I'm trying to get a complete set of buckets for a given dataset, even if no records exist for some buckets.
For example, I want to display totals by day of week, with zero total for days with no ...
- 153
0
votes
1
answer
234
views
Quarter Equivalent for MySQL Data Summation
I am using the following mySQL query for summing up "volume" column for each month
SELECT DATE_FORMAT(date, "%m-%Y") AS Month,
SUM(vol)
FROM tbl
GROUP BY DATE_FORMAT(date, "%m-%Y")
order ...
- 883
1
vote
1
answer
58
views
Grab the correct set of data from database Mysql/MariaDB
Consider the following data from a table
SELECT * FROM messages;
+----+------------+------------+----------------------------------------------------------------+------------+
| id | did_from | ...
- 121
0
votes
1
answer
52
views
MS SQL Server Query Date functions to MySQL
I have this MS SQL Server query:
SELECT DATEPART(MONTH, si.score_date),
MAX(si.score_value)
FROM score_info AS si
WHERE si.score_date >= DATEADD(MONTH, -3, DATEADD(MONTH, DATEDIFF(MONTH, ...
- 11
0
votes
2
answers
310
views
Counting all rows in column with two different date conditions
I'm trying to turn two count queries with date conditions (the ones below) into one query.
SELECT COUNT(*) as yesterday FROM orders WHERE DATE(timedate) = DATE(NOW() - INTERVAL 1 DAY)
SELECT COUNT(*)...
- 65
0
votes
1
answer
48
views
How to get none matching columns and matching columns with two sql tables
I have two table incomes and expenses, I want to includes the none matching columns to the third table so I can be able to sum and group incomes and expenses with months and sum the Income column to ...
- 185
1
vote
1
answer
27
views
Query for datediff between rows where value was unchanged, grouped by the value
I am looking to query an invoices table which also keeps track of which "program" customers were on at the time of their purchases (not the best way of storing that but it is what it is and I'm left ...
- 134
0
votes
1
answer
588
views
SELECT MAX MIN value of temperature CURRENT DAY but last YEARS from ALL history
I would like to ask you for help with SQL.
I have got database with where are 4 colums. ( time, temp, hum, press )
In this table are data from my meteo station.
Right now in this table are 110000 ...
- 3
2
votes
1
answer
450
views
MYSQL return zero in date not present, and COUNT how many rows are present with a specific date
I have a table named calendar with a single column (mydate DATE).
MYDATE
2020-01-01
2020-01-02
2020-01-03
...
I have also a table named delivery with three columns (id PK, giorno DATE, totale FLOAT)...
- 33
0
votes
1
answer
52
views
How can i fetch data from SQL till a certain period of time [closed]
I have data of merchants and the number of transactions of that merchant with time stamp.
My question is what if I want to count the number of transaction till July 2019, then till august, then till ...
- 11
1
vote
3
answers
194
views
How to edit this query to get last 7 days visits even if there are no visits on specific days? [duplicate]
I have the following query that returns the dates first_visit starting from today and 7 days back, as well as the visitors hash per day:
SET time_zone= '{$company_timezone}';
SELECT DATE( first_visit ...
- 8,816
1
vote
1
answer
148
views
Get report of last week of all days including days with 0 count
I am trying to get a report of last week entries/tickets and show 0 if that day there was none entries and then show it in google line chart for a project.MySQL table look like this
------------------...
- 581