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

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'

4 answers

  • answered 2022-05-07 06:07 Kendle

    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';
    

  • answered 2022-05-07 06:09 Elisha John

    --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
    

  • answered 2022-05-07 06:10 D-Shih

    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
    

  • answered 2022-05-07 06:59 Meet Soni

    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
    

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum