How to get the average for Day Wise in mysql?-CodePudding

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.

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;

CodePudding user response：

First change column name from Date to something else. Its reserved. 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

CodePudding user response：

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

That's why I made a guess ....