how to get weeklytotal and yesterday record in mysql in one table-CodePudding

Hi Everyone i am trying to implement query to get weekly and yesterday data in same table, dummy output i have shared below, if yesterday not exist as per employee_id it should we showing 0 also as per my table week start from monday and end at sunday.please help me out how to query this get weekly_total and yesterday record and one table.

Table name-dailydata-

Sample data

employee_id	date	total
20	2022-04-25	10
20	2022-04-26	20
20	2022-04-27	20
20	2022-04-28	10
20	2022-04-29	20
20	2022-04-30	30
20	2022-04-31	40
20	2022-05-01	50
40	2022-04-26	20

expected output

employee_id	weekly_total	yesterday_record
20	200	40
40	20	0

mysql query to get weekly data

select employee_id,sum(total) as week_total from dailydata where date between '2022-04-25' and '2022-05-01'

CodePudding user response：

You can try to use the condition aggregate function to make it.

We might add non-aggregate columns in the group by when we are using aggregate functions.

select employee_id,
       SUM(total) as week_total,
       SUM(CASE WHEN DATEDIFF('2022-05-01',date) = 1 THEN total ELSE 0 END) yesterday_record   
from dailydata t1
where date between '2022-04-25' and '2022-05-01'
GROUP BY employee_id

CodePudding user response：

You can use a case in the query to get yesterdays data, as long as the where does not exclude it, which is the case in the second query.
Once you have understood the principal you can define the date range so that it is calculated dynamically when you run the script if what you want is to see yesterday's figure and the last 7 days total.
You can also get yesterday using SUBDATE(NOW(),1) which is shorter.


select 
  employee_id,
  sum(total) as week_total ,
  sum(case when date = DATE_SUB(CURDATE(), INTERVAL 1 DAY) 
          then total else 0 end as yesterday
from dailydata 
where date between 
DATE_SUB(CURDATE(), INTERVAL 1 WEEK) 
and DATE_SUB(CURDATE(), INTERVAL 1 DAY) ;

select 
  employee_id,
  sum(total) as week_total ,
  sum(case when date = DATE_SUB(CURDATE(), INTERVAL 1 DAY) 
          then total else 0 end as yesterday
from dailydata 
where date between '2022-04-25' and '2022-05-01';

CodePudding user response：

--start of this week

SELECT DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0)

--end of this week

SELECT DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 6)

--yesterday

select GETDATE() -1

--Your Qurey

select employee_id,sum(total) as week_total, (select sum(total) as week_total from dailydata b where [date] = CONVERT(date, GETDATE() -1 ) and a.employee_id = b.employee_id) as yesterday_record 
from dailydata a 
where [date] between DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0) and DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 6)
Group by employee_id

CodePudding user response：

Hope this may help you, You just need to use the aggregate function in the case of IFNULL.

DBFiddle URL: Click Here

For the start of the week

SELECT SUBDATE(CURDATE(), weekday(CURDATE())); --Start of week

For the end of the week

SELECT DATE(CURDATE()   INTERVAL (6 - WEEKDAY(CURDATE())) DAY); --End of week

Hereby SQL query for getting employe wise total and yesterday total. If yesterday's total doesn't exist so for that Have used IFNULL. Just used SUBDATE for getting the start and end of the week date by passing current date.

SELECT employee_id, 
IFNULL(SUM(total),0) AS total,
IFNULL(SUM(CASE date WHEN subdate(CURDATE(), 1) THEN total ELSE 0 END),0) AS yesterday_total
FROM dailydata
WHERE date BETWEEN 
SUBDATE(CURDATE(), weekday(CURDATE())) AND (CURDATE()   INTERVAL (6 - WEEKDAY(CURDATE())) DAY)
GROUP BY employee_id