Solved

Help with MySQL Query

Posted on 2013-01-11
1
387 Views
Last Modified: 2013-01-24
Hi,

I have a mysql database which collects phone Call Detail Records (CDR). The fields of the MySQL data / Table is somewhat as follows (i've cut down all whats not necessary):

Phone_Number
Channel_Number
Call_Duration

I need to write a query which would give me a output showing the Average Call Duration of each channel, but the average should be calculated only based on the last 10 calls per channel.

My current query looks as follows:

select Channel_Number, count(0) as cnt, avg(Call_Duration) as ACD from CDR_Table group by Channel_Number

Open in new window


But the above query would give the output based on ALL records available in the table. I just need to only get the average for the last 10 calls per channel.

How can this be achieved?

Thanks!
0
Comment
Question by:shaf81
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 200 total points
ID: 38767450
you need to first extract to have the 10 last calls per channel, this into a temp table...
for this, you need of course to have a field which indicates the date of the call, which I presume you have in the same table ...

anyhow, please check this article on how to retrieve the N "last" calls per "group = channel"
http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question