Question 21394 in Mysql Question posted in
The official MySQL documentation can be found here.

How to get the average for Day Wise in mysql?

I have a table as follows with below three columns

Date      table_name    count
1/6/2023    table1    5161454381
1/6/2023    table2    286759521
1/6/2023    table3    43756630
1/6/2023    table4    27032087
1/6/2023    table5    44189394
1/6/2023    table6    63475022
1/5/2023    table1    5362300063
1/5/2023    table2    289459360
1/5/2023    table3    59042261
1/5/2023    table4    37256587
1/5/2023    table5    55511764
1/5/2023    table6    77917391
1/4/2023    table1    5316875841
1/4/2023    table2    282894202
1/4/2023    table3    57226525
1/4/2023    table4    36150830
1/4/2023    table5    54099874
1/4/2023    table6    75897787
1/3/2023    table1    5301932742
1/3/2023    table2    281278819

I need to add a average for each day as well as each table. As an example,
Lets say the Date is 2023-01-06 and table is table1, I need to get the average for last 2 week same week day. Mean that the average of 2023-01-06(friday) is caclulcated by using 2022-12-30 and 2022-12-23. Both are Friday. Then it should displayed. Then I need to show it as a pivot table.

enter image description here

I tried as below, but average taking part is not correct, can some one help to change that part to get the required output?

SELECT
    t1.table_name as table_name,
    MAX(CASE WHEN table_date=DATE_SUB(CURDATE(), INTERVAL 4 DAY) THEN count END) AS '$date04',
    MAX(CASE WHEN table_date=DATE_SUB(CURDATE(), INTERVAL 3 DAY) THEN count END) AS '$date03',
    MAX(CASE WHEN table_date=DATE_SUB(CURDATE(), INTERVAL 2 DAY) THEN count END) AS '$date02',
    MAX(CASE WHEN table_date=DATE_SUB(CURDATE(), INTERVAL 1 DAY) THEN count END) AS '$date01',
    t2.AVG
FROM tbl t1
INNER JOIN
(
    SELECT table_name, AVG(count) AS AVG
    FROM tbl
    where table_date >= DATE(NOW() - INTERVAL 14 DAY)
    GROUP BY table_name
) t2
    ON t2.table_name = t1.table_name
where table_date >= DATE(NOW() - INTERVAL 4 DAY) and t1.table_name not in ('table7')
group by t1.table_name;
Tags:

2

Answers


  1. 0

    I am not sure If I follow you correctly when you describe what you desire.

    That’s why I made a guess …. 🤔😕😉

    SELECT
  table_name,
  MAX(CASE WHEN `Date` = '2023-01-03' THEN A ELSE 0 END) as Avg3,
  MAX(CASE WHEN `Date` = '2022-12-20' THEN A ELSE 0 END) as Avg3_Min2wk,
  MAX(CASE WHEN `Date` = '2023-01-04' THEN A ELSE 0 END) as Avg4,
  MAX(CASE WHEN `Date` = '2022-12-21' THEN A ELSE 0 END) as Avg4_Min2wk,
  MAX(CASE WHEN `Date` = '2023-01-05' THEN A ELSE 0 END) as Avg5,
  MAX(CASE WHEN `Date` = '2022-12-22' THEN A ELSE 0 END) as Avg5_Min2wk,
  MAX(CASE WHEN `Date` = '2023-01-06' THEN A ELSE 0 END) as Avg6,
  MAX(CASE WHEN `Date` = '2022-12-23' THEN A ELSE 0 END) as Avg6_Min2wk
FROM (
  SELECT 
    table_name,
    `Date`,
    AVG(`count`) as A
  FROM tbl
  GROUP BY table_name, `Date`
  ) x
GROUP BY table_name

    output:

    table_name Avg3 Avg3_Min2wk Avg4 Avg4_Min2wk Avg5 Avg5_Min2wk Avg6 Avg6_Min2wk
    table1 5301932742.0000 0.0000 5316875841.0000 0.0000 5362300063.0000 0.0000 5161454381.0000 0.0000
    table2 281278819.0000 0.0000 282894202.0000 0.0000 289459360.0000 0.0000 286759521.0000 0.0000
    table3 0.0000 0.0000 57226525.0000 0.0000 59042261.0000 0.0000 43756630.0000 0.0000
    table4 0.0000 0.0000 36150830.0000 0.0000 37256587.0000 0.0000 27032087.0000 0.0000
    table5 0.0000 0.0000 54099874.0000 0.0000 55511764.0000 0.0000 44189394.0000 0.0000
    table6 0.0000 0.0000 75897787.0000 0.0000 77917391.0000 0.0000 63475022.0000 0.0000
    Login or Signup to reply.
  2. 0

    First change column name from Date to something else. I used Datee

    Try

    WITH  cte1 AS (SELECT DISTINCT(table_name) FROM tbl)
SELECT table_name,
(SELECT count FROM tbl WHERE Datee=DATE_SUB(CURDATE(), INTERVAL 3 DAY) AND tbl.table_name=cte1.table_name) AS '$date04',
(SELECT AVG(count) FROM tbl WHERE Datee IN (DATE_SUB(CURDATE(), INTERVAL 3+7 DAY),DATE_SUB(CURDATE(), INTERVAL 3+14 DAY)) AND 
tbl.table_name=cte1.table_name) AS avg1,
(SELECT count FROM tbl WHERE Datee=DATE_SUB(CURDATE(), INTERVAL 2 DAY) AND tbl.table_name=cte1.table_name) AS '$date03',
(SELECT AVG(count) FROM tbl WHERE Datee IN (DATE_SUB(CURDATE(), INTERVAL 2+7 DAY),DATE_SUB(CURDATE(), INTERVAL 2+14 DAY)) AND 
tbl.table_name=cte1.table_name) AS avg2,
(SELECT count FROM tbl WHERE Datee=DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND tbl.table_name=cte1.table_name) AS '$date02',
(SELECT AVG(count) FROM tbl WHERE Datee IN (DATE_SUB(CURDATE(), INTERVAL 1+7 DAY),DATE_SUB(CURDATE(), INTERVAL 1+14 DAY)) AND 
tbl.table_name=cte1.table_name) AS avg3,
(SELECT count FROM tbl WHERE Datee=DATE_SUB(CURDATE(), INTERVAL 0 DAY) AND tbl.table_name=cte1.table_name) AS '$date01',
(SELECT AVG(count) FROM tbl WHERE Datee IN (DATE_SUB(CURDATE(), INTERVAL 0+7 DAY),DATE_SUB(CURDATE(), INTERVAL 0+14 DAY)) AND 
tbl.table_name=cte1.table_name) AS avg4
 FROM cte1;

    Also, you will probably need dynamic sql if you want to use date value like 03-01-2023 as actual column name

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search