From: Martin Gainty Date: July 31 2010 12:43pm Subject: RE: query results group/summed by interval List-Archive: http://lists.mysql.com/mysql/222387 Message-Id: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="_2280b14e-abfd-489c-9af1-34517039fc99_" --_2280b14e-abfd-489c-9af1-34517039fc99_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable no that would give you the count for each second interval instead of using = the interval variable 5=20 Aveeks floor: FLOOR(X) Returns the largest integer value not greater than X.=20 =20 1st (seconds/5) interval example 5/5=3D1 floor(5/5) =3D 1 supplied value would truncate and give you the int not greater than X then multiply by 5 1*5=3D5 is correct =20 Aveeks sum function: SUM([DISTINCT] expr)=20 Returns the sum of expr. If the return set has no rows=2C SUM() returns NUL= L. The DISTINCT keyword can be used in MySQL 5.0 to sum only the distinct v= alues of expr.=20 SUM() returns NULL if there were no matching rows.=20 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=20 =20 2nd(seconds/5) interval example 10/5=3D2 floor(10/5)=3D2 supplied value would truncate and give you the int not greater than X then multiply by 5 2*5=3D10 is correct =20 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 =20 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) =20 Martin Gainty=20 ______________________________________________=20 Verzicht und Vertraulichkeitanmerkung/Note de d=E9ni et de confidentialit= =E9 Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaeng= er sein=2C so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiter= leitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient l= ediglich dem Austausch von Informationen und entfaltet keine rechtliche Bin= dungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen w= ir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut =EAtre privil=E9gi=E9. Si vous n'=EAtes= pas le destinataire pr=E9vu=2C nous te demandons avec bont=E9 que pour sat= isfaire informez l'exp=E9diteur. N'importe quelle diffusion non autoris=E9e= ou la copie de ceci est interdite. Ce message sert =E0 l'information seule= ment et n'aura pas n'importe quel effet l=E9galement obligatoire. =C9tant d= onn=E9 que les email peuvent facilement =EAtre sujets =E0 la manipulation= =2C nous ne pouvons accepter aucune responsabilit=E9 pour le contenu fourni= . =20 > Date: Sat=2C 31 Jul 2010 10:31:43 +0700 > From: cuong.manh@stripped > To: aveekm@stripped > CC: mustafa.pk@stripped=3B mysql@stripped > Subject: Re: query results group/summed by interval >=20 > Hi Aveek=2C >=20 > I think Ghulam just want to count calls for each intervals > so the query should looks like this: >=20 > select count(*) as total_calls=2C queue_seconds > from calls group by queue_seconds order by total_calls=3B >=20 >=20 > ----- Original Message ----- > From: "Aveek Misra" > To: "Ghulam Mustafa" =2C mysql@stripped > Sent: Tuesday=2C July 27=2C 2010 5:54:13 PM > Subject: RE: query results group/summed by interval >=20 > try this ... >=20 > select 5 * floor(seconds/5) as start=2C 5 * floor(seconds/5) + 5 as end= =2C sum(calls) from calls group by 5 * floor(seconds/5)=3B >=20 > This should give you an output of the type >=20 > +-------+------+------------+ > | start | end | sum(calls) | > +-------+------+------------+ > | 0 | 5 | 387 | > | 5 | 10 | 225 | > | 10 | 15 | 74 | > +-------+------+------------+ >=20 >=20 > Thanks > Aveek > ________________________________________ > From: Ghulam Mustafa [mustafa.pk@stripped] > Sent: Tuesday=2C July 27=2C 2010 3:53 PM > To: mysql@stripped > Subject: query results group/summed by interval >=20 > Hi everyone=2C >=20 > i have two columns (seconds=2C number of calls)=2C i need to produce a > report which will show total number of calls in intervals (let'say 10 > seconds interval)=2C i know i can do this programmability in my script bu= t > i was wondering if it's possible to accomplish this behavior within > mysql. for example i have following data. >=20 > +----------+---------------+ > | 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... > ... > +----------+---------------+ >=20 > now result should look like this with a 5 seconds interval. >=20 > +----------+---------------+ > | count(*) | queue_seconds | > +----------+---------------+ > | 250 | 0.00 | > | 168 | 5.00 | > | 268 | 10.00 | > ... > ... and so on... > ... > +----------+---------------+ >=20 > i would really appreciate your help. >=20 > Best Regards. >=20 > -- > Ghulam Mustafa >=20 > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=3Daveekm@stripped >=20 >=20 > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dcuong.manh@vienthong= so.com >=20 >=20 > --=20 > Best Regards=2C > Cuongmc. >=20 > --=20 > Nguyen Manh Cuong > Phong Ky Thuat - Cong ty Vien Thong So - VTC > Dien thoai: 0912051542 > Gmail : philipscuong@stripped > YahooMail : philipscuong@stripped >=20 > --=20 > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmgainty@stripped >=20 = --_2280b14e-abfd-489c-9af1-34517039fc99_--