Hi all, Aveeks solution should work if you have at least one call for each intervall. It's the classical GROUP BY solution that only works on the available dataset. Although it should work pretty well in the cited scenario, you will miss intervals (from a "all intervals report" point of view) if indeed there are intervals (of more than 5 minutes, in this example) when there were no calls at all.
I had a somewhat similar problem (running the second scenario, though) and this is the solution I setup (this was a Data Warehouse and that's why you'll read about partition pruning, dataset was dozens of Gigs): http://gpshumano.blogs.dri.pt/2009/09/28/finding-for-each-time-interval-how-many-records-are-ocurring-during-that-interval/ This might become handy if Ghulam understands the differences between my scenario and his. Hope that helps, -NT Quoting Martin Gainty <mgai...@hotmail.com>: > > no that would give you the count for each second interval instead of using > the interval variable 5 > > Aveeks floor: > FLOOR(X) Returns the largest integer value not greater than X. > > 1st (seconds/5) interval example > 5/5=1 > floor(5/5) = 1 > supplied value would truncate and give you the int not greater than X > then multiply by 5 > 1*5=5 > is correct > > Aveeks sum function: > SUM([DISTINCT] expr) > Returns the sum of expr. If the return set has no rows, SUM() returns NULL. > The DISTINCT keyword can be used in MySQL 5.0 to sum only the distinct values > of expr. > SUM() returns NULL if there were no matching rows. > sum(calls) from calls group by 5 * floor(seconds/5) > sum(calls) from calls group by 5 * floor(5/5) > sum(calls) from class group by 5 * 1 > sum(calls) from class group by 5 > is correct > > 2nd(seconds/5) interval example > 10/5=2 > floor(10/5)=2 > supplied value would truncate and give you the int not greater than X > then multiply by 5 > 2*5=10 > is correct > > Aveeks sum function > sum(calls) from calls group by 5 * floor(seconds/5) > sum(calls) from calls group by 5 * floor(10/5) > sum(calls) from class group by 5 * 2 > sum(calls) from class group by 10 > would be applicable only if the interval was 10 > > Aveek if your interval is 5 change: > sum(calls) from calls group by 5 * floor(seconds/5) > to > sum(calls) from calls group by floor(seconds/5) > > Martin Gainty > ______________________________________________ > Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité > > Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger > sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung > oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich > dem Austausch von Informationen und entfaltet keine rechtliche > Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen > wir keine Haftung fuer den Inhalt uebernehmen. > Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le > destinataire prévu, nous te demandons avec bonté que pour satisfaire informez > l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci > est interdite. Ce message sert à l'information seulement et n'aura pas > n'importe quel effet légalement obligatoire. Étant donné que les email > peuvent facilement être sujets à la manipulation, nous ne pouvons accepter > aucune responsabilité pour le contenu fourni. > > > > > > > Date: Sat, 31 Jul 2010 10:31:43 +0700 > > From: cuong.m...@vienthongso.com > > To: ave...@yahoo-inc.com > > CC: mustafa...@gmail.com; mysql@lists.mysql.com > > Subject: Re: query results group/summed by interval > > > > Hi Aveek, > > > > I think Ghulam just want to count calls for each intervals > > so the query should looks like this: > > > > select count(*) as total_calls, queue_seconds > > from calls group by queue_seconds order by total_calls; > > > > > > ----- Original Message ----- > > From: "Aveek Misra" <ave...@yahoo-inc.com> > > To: "Ghulam Mustafa" <mustafa...@gmail.com>, mysql@lists.mysql.com > > Sent: Tuesday, July 27, 2010 5:54:13 PM > > Subject: RE: query results group/summed by interval > > > > try this ... > > > > select 5 * floor(seconds/5) as start, 5 * floor(seconds/5) + 5 as end, > sum(calls) from calls group by 5 * floor(seconds/5); > > > > This should give you an output of the type > > > > +-------+------+------------+ > > | start | end | sum(calls) | > > +-------+------+------------+ > > | 0 | 5 | 387 | > > | 5 | 10 | 225 | > > | 10 | 15 | 74 | > > +-------+------+------------+ > > > > > > Thanks > > Aveek > > ________________________________________ > > From: Ghulam Mustafa [mustafa...@gmail.com] > > Sent: Tuesday, July 27, 2010 3:53 PM > > To: mysql@lists.mysql.com > > Subject: query results group/summed by interval > > > > Hi everyone, > > > > i have two columns (seconds, number of calls), i need to produce a > > report which will show total number of calls in intervals (let'say 10 > > seconds interval), i know i can do this programmability in my script but > > i was wondering if it's possible to accomplish this behavior within > > mysql. for example i have following data. > > > > +----------+---------------+ > > | calls | queue_seconds | > > +----------+---------------+ > > | 250 | 0.00 | > > | 28 | 1.00 | > > | 30 | 2.00 | > > | 56 | 3.00 | > > | 23 | 4.00 | > > | 31 | 5.00 | > > | 33 | 6.00 | > > | 50 | 7.00 | > > | 49 | 8.00 | > > | 62 | 9.00 | > > | 74 | 10.00 | > > ... > > ... and so on... > > ... > > +----------+---------------+ > > > > now result should look like this with a 5 seconds interval. > > > > +----------+---------------+ > > | count(*) | queue_seconds | > > +----------+---------------+ > > | 250 | 0.00 | > > | 168 | 5.00 | > > | 268 | 10.00 | > > ... > > ... and so on... > > ... > > +----------+---------------+ > > > > i would really appreciate your help. > > > > Best Regards. > > > > -- > > Ghulam Mustafa > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/mysql?unsub=ave...@yahoo-inc.com > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > http://lists.mysql.com/mysql?unsub=cuong.m...@vienthongso.com > > > > > > -- > > Best Regards, > > Cuongmc. > > > > -- > > Nguyen Manh Cuong > > Phong Ky Thuat - Cong ty Vien Thong So - VTC > > Dien thoai: 0912051542 > > Gmail : philipscu...@gmail.com > > YahooMail : philipscu...@yahoo.com > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com > > > -- Nuno Tavares +351 93 618 40 86 dri Consultoria Informatica -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
