CODEWITHSUNDEEP
mysqlsqlperformancequery-optimization
John Guan
John Guan

Reputation: 744

How to optimize MySQL queries that has calculations

I have a fully working query but it takes 1.5 secs to load. I am pretty new to MySQL so I know my queries are not the best but is there a way to optimize this? I will be having 5-6 of these queries and it'll take over 10 seconds to load them all.

Select * From
    (SELECT ROUND((SUM(`I_Talk_Time_Sec`) + SUM(`Hold_Time_Sec`) + SUM(`I_Work_Time_Sec`) + SUM(I_AUX_Out_Time_Sec)) / SUM(`Calls_Handled_Ct`)) as `JAN` FROM `enterprise_rep_agent_stats` WHERE `Employee_ID` = '$id' AND `Month_Date` = '2013-01-01') As `JAN`,
    (SELECT ROUND((SUM(`I_Talk_Time_Sec`) + SUM(`Hold_Time_Sec`) + SUM(`I_Work_Time_Sec`) + SUM(I_AUX_Out_Time_Sec)) / SUM(`Calls_Handled_Ct`)) as `FEB` FROM `enterprise_rep_agent_stats` WHERE `Employee_ID` = '$id' AND `Month_Date` = '2013-02-01') As `FEB`,
    (SELECT ROUND((SUM(`I_Talk_Time_Sec`) + SUM(`Hold_Time_Sec`) + SUM(`I_Work_Time_Sec`) + SUM(I_AUX_Out_Time_Sec)) / SUM(`Calls_Handled_Ct`)) as `MAR` FROM `enterprise_rep_agent_stats` WHERE `Employee_ID` = '$id' AND `Month_Date` = '2013-03-01') As `MAR`,
    (SELECT ROUND((SUM(`I_Talk_Time_Sec`) + SUM(`Hold_Time_Sec`) + SUM(`I_Work_Time_Sec`) + SUM(I_AUX_Out_Time_Sec)) / SUM(`Calls_Handled_Ct`)) as `APR` FROM `enterprise_rep_agent_stats` WHERE `Employee_ID` = '$id' AND `Month_Date` = '2013-04-01') As `APR`,
    (SELECT ROUND((SUM(`I_Talk_Time_Sec`) + SUM(`Hold_Time_Sec`) + SUM(`I_Work_Time_Sec`) + SUM(I_AUX_Out_Time_Sec)) / SUM(`Calls_Handled_Ct`)) as `MAY` FROM `enterprise_rep_agent_stats` WHERE `Employee_ID` = '$id' AND `Month_Date` = '2013-05-01') As `MAY`,
    (SELECT ROUND((SUM(`I_Talk_Time_Sec`) + SUM(`Hold_Time_Sec`) + SUM(`I_Work_Time_Sec`) + SUM(I_AUX_Out_Time_Sec)) / SUM(`Calls_Handled_Ct`)) as `JUN` FROM `enterprise_rep_agent_stats` WHERE `Employee_ID` = '$id' AND `Month_Date` = '2013-06-01') As `JUN`,
    (SELECT ROUND((SUM(`I_Talk_Time_Sec`) + SUM(`Hold_Time_Sec`) + SUM(`I_Work_Time_Sec`) + SUM(I_AUX_Out_Time_Sec)) / SUM(`Calls_Handled_Ct`)) as `JUL` FROM `enterprise_rep_agent_stats` WHERE `Employee_ID` = '$id' AND `Month_Date` = '2013-07-01') As `JUL`,
    (SELECT ROUND((SUM(`I_Talk_Time_Sec`) + SUM(`Hold_Time_Sec`) + SUM(`I_Work_Time_Sec`) + SUM(I_AUX_Out_Time_Sec)) / SUM(`Calls_Handled_Ct`)) as `AUG` FROM `enterprise_rep_agent_stats` WHERE `Employee_ID` = '$id' AND `Month_Date` = '2013-08-01') As `AUG`,
    (SELECT ROUND((SUM(`I_Talk_Time_Sec`) + SUM(`Hold_Time_Sec`) + SUM(`I_Work_Time_Sec`) + SUM(I_AUX_Out_Time_Sec)) / SUM(`Calls_Handled_Ct`)) as `SEP` FROM `enterprise_rep_agent_stats` WHERE `Employee_ID` = '$id' AND `Month_Date` = '2013-09-01') As `SEP`,
    (SELECT ROUND((SUM(`I_Talk_Time_Sec`) + SUM(`Hold_Time_Sec`) + SUM(`I_Work_Time_Sec`) + SUM(I_AUX_Out_Time_Sec)) / SUM(`Calls_Handled_Ct`)) as `OCT` FROM `enterprise_rep_agent_stats` WHERE `Employee_ID` = '$id' AND `Month_Date` = '2013-10-01') As `OCT`,
    (SELECT ROUND((SUM(`I_Talk_Time_Sec`) + SUM(`Hold_Time_Sec`) + SUM(`I_Work_Time_Sec`) + SUM(I_AUX_Out_Time_Sec)) / SUM(`Calls_Handled_Ct`)) as `NOV` FROM `enterprise_rep_agent_stats` WHERE `Employee_ID` = '$id' AND `Month_Date` = '2013-11-01') As `NOV`,
    (SELECT ROUND((SUM(`I_Talk_Time_Sec`) + SUM(`Hold_Time_Sec`) + SUM(`I_Work_Time_Sec`) + SUM(I_AUX_Out_Time_Sec)) / SUM(`Calls_Handled_Ct`)) as `DEC` FROM `enterprise_rep_agent_stats` WHERE `Employee_ID` = '$id' AND `Month_Date` = '2013-12-01') As `DEC`,
    (SELECT ROUND(SUM(TotalAHT) / CallHandled) as Total_Metric
        FROM
        (SELECT (ROUND((SUM(`I_Talk_Time_Sec`) + SUM(`Hold_Time_Sec`) + SUM(`I_Work_Time_Sec`) + SUM(I_AUX_Out_Time_Sec)) / SUM(`Calls_Handled_Ct`)) * SUM(`Calls_Handled_Ct`)) as `TotalAHT` FROM `enterprise_rep_agent_stats` WHERE `Employee_ID` = '$id' AND `Month_Date` = '2013-01-01' UNION ALL
        SELECT (ROUND((SUM(`I_Talk_Time_Sec`) + SUM(`Hold_Time_Sec`) + SUM(`I_Work_Time_Sec`) + SUM(I_AUX_Out_Time_Sec)) / SUM(`Calls_Handled_Ct`)) * SUM(`Calls_Handled_Ct`)) as `TotalAHT` FROM `enterprise_rep_agent_stats` WHERE `Employee_ID` = '$id' AND `Month_Date` = '2013-02-01' UNION ALL
        SELECT (ROUND((SUM(`I_Talk_Time_Sec`) + SUM(`Hold_Time_Sec`) + SUM(`I_Work_Time_Sec`) + SUM(I_AUX_Out_Time_Sec)) / SUM(`Calls_Handled_Ct`)) * SUM(`Calls_Handled_Ct`)) as `TotalAHT` FROM `enterprise_rep_agent_stats` WHERE `Employee_ID` = '$id' AND `Month_Date` = '2013-03-01' UNION ALL
        SELECT (ROUND((SUM(`I_Talk_Time_Sec`) + SUM(`Hold_Time_Sec`) + SUM(`I_Work_Time_Sec`) + SUM(I_AUX_Out_Time_Sec)) / SUM(`Calls_Handled_Ct`)) * SUM(`Calls_Handled_Ct`)) as `TotalAHT` FROM `enterprise_rep_agent_stats` WHERE `Employee_ID` = '$id' AND `Month_Date` = '2013-04-01' UNION ALL
        SELECT (ROUND((SUM(`I_Talk_Time_Sec`) + SUM(`Hold_Time_Sec`) + SUM(`I_Work_Time_Sec`) + SUM(I_AUX_Out_Time_Sec)) / SUM(`Calls_Handled_Ct`)) * SUM(`Calls_Handled_Ct`)) as `TotalAHT` FROM `enterprise_rep_agent_stats` WHERE `Employee_ID` = '$id' AND `Month_Date` = '2013-05-01' UNION ALL
        SELECT (ROUND((SUM(`I_Talk_Time_Sec`) + SUM(`Hold_Time_Sec`) + SUM(`I_Work_Time_Sec`) + SUM(I_AUX_Out_Time_Sec)) / SUM(`Calls_Handled_Ct`)) * SUM(`Calls_Handled_Ct`)) as `TotalAHT` FROM `enterprise_rep_agent_stats` WHERE `Employee_ID` = '$id' AND `Month_Date` = '2013-06-01' UNION ALL
        SELECT (ROUND((SUM(`I_Talk_Time_Sec`) + SUM(`Hold_Time_Sec`) + SUM(`I_Work_Time_Sec`) + SUM(I_AUX_Out_Time_Sec)) / SUM(`Calls_Handled_Ct`)) * SUM(`Calls_Handled_Ct`)) as `TotalAHT` FROM `enterprise_rep_agent_stats` WHERE `Employee_ID` = '$id' AND `Month_Date` = '2013-07-01' UNION ALL
        SELECT (ROUND((SUM(`I_Talk_Time_Sec`) + SUM(`Hold_Time_Sec`) + SUM(`I_Work_Time_Sec`) + SUM(I_AUX_Out_Time_Sec)) / SUM(`Calls_Handled_Ct`)) * SUM(`Calls_Handled_Ct`)) as `TotalAHT` FROM `enterprise_rep_agent_stats` WHERE `Employee_ID` = '$id' AND `Month_Date` = '2013-08-01' UNION ALL
        SELECT (ROUND((SUM(`I_Talk_Time_Sec`) + SUM(`Hold_Time_Sec`) + SUM(`I_Work_Time_Sec`) + SUM(I_AUX_Out_Time_Sec)) / SUM(`Calls_Handled_Ct`)) * SUM(`Calls_Handled_Ct`)) as `TotalAHT` FROM `enterprise_rep_agent_stats` WHERE `Employee_ID` = '$id' AND `Month_Date` = '2013-09-01' UNION ALL
        SELECT (ROUND((SUM(`I_Talk_Time_Sec`) + SUM(`Hold_Time_Sec`) + SUM(`I_Work_Time_Sec`) + SUM(I_AUX_Out_Time_Sec)) / SUM(`Calls_Handled_Ct`)) * SUM(`Calls_Handled_Ct`)) as `TotalAHT` FROM `enterprise_rep_agent_stats` WHERE `Employee_ID` = '$id' AND `Month_Date` = '2013-10-01' UNION ALL
        SELECT (ROUND((SUM(`I_Talk_Time_Sec`) + SUM(`Hold_Time_Sec`) + SUM(`I_Work_Time_Sec`) + SUM(I_AUX_Out_Time_Sec)) / SUM(`Calls_Handled_Ct`)) * SUM(`Calls_Handled_Ct`)) as `TotalAHT` FROM `enterprise_rep_agent_stats` WHERE `Employee_ID` = '$id' AND `Month_Date` = '2013-11-01' UNION ALL
        SELECT (ROUND((SUM(`I_Talk_Time_Sec`) + SUM(`Hold_Time_Sec`) + SUM(`I_Work_Time_Sec`) + SUM(I_AUX_Out_Time_Sec)) / SUM(`Calls_Handled_Ct`)) * SUM(`Calls_Handled_Ct`)) as `TotalAHT` FROM `enterprise_rep_agent_stats` WHERE `Employee_ID` = '$id' AND `Month_Date` = '2013-12-01') As TotalAHT, 
        (SELECT SUM(`Calls_Handled_Ct`) as `CallHandled` FROM `enterprise_rep_agent_stats` WHERE `Employee_ID` = '$id') As CallHandled) As `Total_Metric`

EDIT

With the answer below, I could get the numbers I want. Is there a way to make the resault per column instead of everything in 1 column?

Expected resaults:

JAN     FEB     MAR     APR     MAY     JUN     JUL     AUG     
148     168     175     160     165     145     162     143

Upvotes: 1

Views: 91

Answers (2)

joostschouten
joostschouten

Reputation: 3893

You seem to be looking for month grouped statistics and in stead of using grouping you do a query per month. I would suggest something like:

SELECT 
    ROUND(
           (
            SUM(`I_Talk_Time_Sec`) + 
            SUM(`Hold_Time_Sec`) + 
            SUM(`I_Work_Time_Sec`) + 
            SUM(I_AUX_Out_Time_Sec)
           ) / SUM(`Calls_Handled_Ct`)
         ) AS time_spent, 
     YEAR(Month_Date) stats_year, 
     MONTH(Month_Date) AS stats_month 
FROM `enterprise_rep_agent_stats`
WHERE `Employee_ID` = '$id'
GROUP BY stats_year, stats_month;

Upvotes: 2

Vatev
Vatev

Reputation: 7590

If I managed to read it correctly (my eyes started to hurt at some point) something like this should work:

SELECT (ROUND((SUM(`I_Talk_Time_Sec`) + SUM(`Hold_Time_Sec`) + SUM(`I_Work_Time_Sec`) + SUM(I_AUX_Out_Time_Sec)) / SUM(`Calls_Handled_Ct`)) * SUM(`Calls_Handled_Ct`)) as sum, 
DATE_FORMAT(NOW(),'%Y-%m') as month
FROM enterprise_rep_agent_stats 
GROUP BY month
WITH ROLLUP

Upvotes: 0

Related Questions