CODEWITHSUNDEEP
sqlt-sql
Rajiv
Rajiv

Reputation: 685

compute data based on column and row

I have a table with the following columns:

    Date    ReportDate  Received    Answered    Average Wait Time
5/1/2015    5/3/2015    10           10           0:00:04
5/1/2015    5/3/2015    10           10           0:00:10
5/1/2015    5/3/2015    4             4           0:00:02
5/1/2015    5/3/2015    5             5           0:00:03
5/2/2015    5/3/2015    10           10           0:00:09
5/2/2015    5/3/2015    9             9           0:00:03
5/2/2015    5/3/2015    12           12           0:00:09
5/2/2015    5/3/2015    15           15           0:00:02
5/2/2015    5/3/2015    20           20           0:00:10
Total                   95           95           0:00:07

I would like to calculate the totals and store it in a different table based on distinct Report Date, like: -

ReportDate  TotalReceivedContacts   TotalAnsweredContacts   TotalAverageWaitTime
5/3/2015        95                       95                      0:00:07

Like this I have many rows based on ReportDate. Please help.

Upvotes: 0

Views: 45

Answers (4)

John
John

Reputation: 1852

This should work:

    SELECT ReportDate, SUM(Received) as ReceivedSum,
           SUM(Answered) as AnsweredSum ,AVG(WaitTime) as WaitTimeAVG
    FROM a
    GROUP BY ReportDate
    ORDER BY ReportDate

see SQL Fiddle

Upvotes: 0

Ajesh VC
Ajesh VC

Reputation: 635

Try this.

INSERT INTO another_table_name (ReportDate,TotalReceivedContacts,TotalAnsweredContacts,TotalAverageWaitTime)
SELECT ReportDate,SUM(Received) AS TotalReceivedContacts,SUM(Answered) AS        
TotalAnsweredContacts, AVG(Average Wait Time) as TotalAverageWaitTime FROM   
table_name GROUP BY ReportDate ORDER BY ReportDate

NB : Where "another_table_name" will be an existing table in your database and this query will insert all the data you selected from "table_name" to "another_table_name".

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

Try this:

SELECT ReportDate, SUM(Received) AS TotalReceivedContacts,
    SUM(Answered) AS TotalAnsweredContacts, AVG(AverageWaitTime) AS TotalAverageWaitTime
FROM Table
GROUP BY ReportDate

Upvotes: 4

Vishnu
Vishnu

Reputation: 375

SELECT SUM(Received) AS TotalReceivedContacts,SUM(Answered) AS   
TotalAnsweredContacts , AVG(WaitTime) AS TotalAverageWaitTime FROM  
YourTableName group by ReportDate

Try this :)

Upvotes: 1

Related Questions