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 >
