List: General Discussion « Previous MessageNext Message » From: Martin Gainty Date: July 31 2010 12:43pm Subject: RE: query results group/summed by interval View as plain text
```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@stripped
> To: aveekm@stripped
> CC: mustafa.pk@stripped; mysql@stripped
> 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" <aveekm@stripped>
> To: "Ghulam Mustafa" <mustafa.pk@stripped>, mysql@stripped
> 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@stripped]
> Sent: Tuesday, July 27, 2010 3:53 PM
> To: mysql@stripped
> 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=1
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
>
> --
> Best Regards,
> Cuongmc.
>
> --
> Nguyen Manh Cuong
> Phong Ky Thuat - Cong ty Vien Thong So - VTC
> Dien thoai: 0912051542
> Gmail : philipscuong@stripped
> YahooMail : philipscuong@stripped
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>

```