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=gcdmg-mysql-2@m.gmane.org

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.pk@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=3Daveekm@yahoo-inc.co=
m


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

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"
To: "Ghulam Mustafa" , 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.pk@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=aveekm@yahoo-inc.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=cuong.manh@vienthongso.co m


--
Best Regards,
Cuongmc.

--
Nguyen Manh Cuong
Phong Ky Thuat - Cong ty Vien Thong So - VTC
Dien thoai: 0912051542
Gmail : philipscuong@gmail.com
YahooMail : philipscuong@yahoo.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: query results group/summed by interval

--_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@vienthongso.com
> To: aveekm@yahoo-inc.com
> CC: mustafa.pk@gmail.com=3B mysql@lists.mysql.com
> 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@lists.mysql.com
> 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@gmail.com]
> Sent: Tuesday=2C July 27=2C 2010 3:53 PM
> To: mysql@lists.mysql.com
> 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@yahoo-inc.com
>=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@gmail.com
> YahooMail : philipscuong@yahoo.com
>=20
> --=20
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmgainty@hotmail.com
>=20
=

--_2280b14e-abfd-489c-9af1-34517039fc99_--

RE: query results group/summed by interval

--_7b9f528f-3d5c-4776-8c6c-9ce261e864e8_
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@vienthongso.com
> To: aveekm@yahoo-inc.com
> CC: mustafa.pk@gmail.com=3B mysql@lists.mysql.com
> 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@lists.mysql.com
> 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@gmail.com]
> Sent: Tuesday=2C July 27=2C 2010 3:53 PM
> To: mysql@lists.mysql.com
> 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@yahoo-inc.com
>=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@gmail.com
> YahooMail : philipscuong@yahoo.com
>=20
> --=20
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmgainty@hotmail.com
>=20
=

--_7b9f528f-3d5c-4776-8c6c-9ce261e864e8_--

RE: query results group/summed by interval

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-ti me-interval-how-many-records-are-ocurring-during-that-interv al/

This might become handy if Ghulam understands the differences between my
scenario and his.

Hope that helps,
-NT




Quoting Martin Gainty :

>
> 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.manh@vienthongso.com
> > To: aveekm@yahoo-inc.com
> > CC: mustafa.pk@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"
> > To: "Ghulam Mustafa" , 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.pk@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=aveekm@yahoo-inc.com
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/mysql?unsub=cuong.manh@vienthongso.co m
> >
> >
> > --
> > Best Regards,
> > Cuongmc.
> >
> > --
> > Nguyen Manh Cuong
> > Phong Ky Thuat - Cong ty Vien Thong So - VTC
> > Dien thoai: 0912051542
> > Gmail : philipscuong@gmail.com
> > YahooMail : philipscuong@yahoo.com
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/mysql?unsub=mgainty@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=gcdmg-mysql-2@m.gmane.org

RE: query results group/summed by interval

You could also pre-define your intervals in a subquery using UNION and join=
that to your original table like so:

select ifnull(sum(calls)=2C 0) as calls=2C n as queue_seconds
from=20
(select 0 as n union=20
select 5 union=20
select 10 union=20
select 15) as step
left join calls on calls.queue_seconds > (step.n - 5) and calls.queue_secon=
ds <=3D step.n
group by n=3B

+-------+---------------+
| calls | queue_seconds |
+-------+---------------+
|   250 |             0 |
|   168 |             5 |
|   268 |          =A0 10 |
|     0 |          =A0 15 |
+-------+---------------+
4 rows in set (0.00 sec)

-Travis

----------------------------------------
> Date: Sun=2C 1 Aug 2010 13:16:36 +0100
> From: nuno.tavares@dri.pt
> To: mgainty@hotmail.com
> CC: cuong.manh@vienthongso.com=3B aveekm@yahoo-inc.com=3B mustafa.pk@gmai=
l.com=3B mysql@lists.mysql.com
> Subject: RE: query results group/summed by interval
>
> Hi all=2C
>
> Aveeks solution should work if you have at least one call for each interv=
all.
> It's the classical GROUP BY solution that only works on the available dat=
aset.
> Although it should work pretty well in the cited scenario=2C you will mis=
s
> intervals (from a "all intervals report" point of view) if indeed there a=
re
> intervals (of more than 5 minutes=2C in this example) when there were no =
calls at
> all.
>
> I had a somewhat similar problem (running the second scenario=2C though) =
and this
> is the solution I setup (this was a Data Warehouse and that's why you'll =
read
> about partition pruning=2C dataset was dozens of Gigs):
>
> http://gpshumano.blogs.dri.pt/2009/09/28/finding-for-each-ti me-interval-h=
ow-many-records-are-ocurring-during-that-interval/
>
> This might become handy if Ghulam understands the differences between my
> scenario and his.
>
> Hope that helps=2C
> -NT
>
>
>
>
> Quoting Martin Gainty :
>
> >
> > no that would give you the count for each second interval instead of us=
ing
> > the interval variable 5
> >
> > Aveeks floor:
> > FLOOR(X) Returns the largest integer value not greater than X.
> >
> > 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
> >
> > Aveeks sum function:
> > SUM([DISTINCT] expr)
> > Returns the sum of expr. If the return set has no rows=2C 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=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
> >
> > 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=E9ni et de confidential=
it=E9
> >
> > Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empf=
aenger
> > sein=2C so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weite=
rleitung
> > oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient ledig=
lich
> > dem Austausch von Informationen und entfaltet keine rechtliche
> > Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails ko=
ennen
> > wir 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 satisfa=
ire informez
> > l'exp=E9diteur. N'importe quelle diffusion non autoris=E9e ou la copie =
de ceci
> > est interdite. Ce message sert =E0 l'information seulement et n'aura pa=
s
> > n'importe quel effet l=E9galement obligatoire. =C9tant donn=E9 que les =
email
> > peuvent facilement =EAtre sujets =E0 la manipulation=2C nous ne pouvons=
accepter
> > aucune responsabilit=E9 pour le contenu fourni.
> >
> >
> >
> >
> >
> > > Date: Sat=2C 31 Jul 2010 10:31:43 +0700
> > > From: cuong.manh@vienthongso.com
> > > To: aveekm@yahoo-inc.com
> > > CC: mustafa.pk@gmail.com=3B mysql@lists.mysql.com
> > > Subject: Re: query results group/summed by interval
> > >
> > > Hi Aveek=2C
> > >
> > > I think Ghulam just want to count calls for each intervals
> > > so the query should looks like this:
> > >
> > > select count(*) as total_calls=2C queue_seconds
> > > from calls group by queue_seconds order by total_calls=3B
> > >
> > >
> > > ----- Original Message -----
> > > From: "Aveek Misra"=20
> > > To: "Ghulam Mustafa" =2C mysql@lists.mysql.com
> > > Sent: Tuesday=2C July 27=2C 2010 5:54:13 PM
> > > Subject: RE: query results group/summed by interval
> > >
> > > try this ...
> > >
> > > select 5 * floor(seconds/5) as start=2C 5 * floor(seconds/5) + 5 as e=
nd=2C
> > sum(calls) from calls group by 5 * floor(seconds/5)=3B
> > >
> > > 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.pk@gmail.com]
> > > Sent: Tuesday=2C July 27=2C 2010 3:53 PM
> > > To: mysql@lists.mysql.com
> > > Subject: query results group/summed by interval
> > >
> > > Hi everyone=2C
> > >
> > > 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 scrip=
t 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=3Daveekm@yahoo-inc=
..com
> > >
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:
> > http://lists.mysql.com/mysql?unsub=3Dcuong.manh@vienthongso. com
> > >
> > >
> > > --
> > > Best Regards=2C
> > > Cuongmc.
> > >
> > > --
> > > Nguyen Manh Cuong
> > > Phong Ky Thuat - Cong ty Vien Thong So - VTC
> > > Dien thoai: 0912051542
> > > Gmail : philipscuong@gmail.com
> > > YahooMail : philipscuong@yahoo.com
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmgainty@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=3Dtravis_ard@hotmail.c=
om
>
=

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg