Grouping by first day of week

[elsenorjose]

I have a report that requires me to provide weekly totals and averages for a group of call center agents. Basically, I need something that looks like this:

Week of AgentID Calls Orders Conv. Rate Avg. Ticket
12/18/2005 3894 175 10 0.057142857 195.75

I currently have a query that gives me daily results:

select
date(caa.call_request_time) 'Date',
caa.agent_id 'Agent ID',
count(*) 'Calls',
SUM(CASE WHEN o.source_session_id IS NOT NULL AND o.order_status = 'S' THEN 1 ELSE 0 END) 'Orders',
SUM(CASE WHEN o.source_session_id IS NOT NULL AND o.order_status = 'S' THEN 1 ELSE 0 END)/count(*) 'Conv. Rate',
AVG(o.menu_amount) 'Avg. Ticket'
from call_at_agent caa
left join orders o on o.source_session_id = caa.uuid
where caa.call_center = 'CALL CENTER NAME'
and month(caa.call_request_time) = 12
group by date, caa.agent_id
order by date desc

But I can't figure out how I would get this to 'roll up' the daily totals into a weekly total.

Thanks.

 

[r937]

select week(`Date`) as week_Date
     , `Agent ID`
     , sum(`Calls`) as sum_Calls
     , sum(`Orders`) as sum_Orders
     , avg(`Conv. Rate`) as avg_ConvRate
     , avg(`Avg. Ticket`) as avg_AvgTicket
  from (
[i]paste your query here[/i]  
       ) as q
group 
    by week_Date
     , `Agent ID`
order 
    by week_Date desc

r937.com | rudy.ca