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
> 
 		 	   		  
Thread
query results group/summed by intervalGhulam Mustafa27 Jul
  • RE: query results group/summed by intervalAveek Misra27 Jul
    • Re: query results group/summed by intervalNguyen Manh Cuong31 Jul
RE: query results group/summed by intervalMartin Gainty31 Jul
RE: query results group/summed by intervalMartin Gainty31 Jul
  • RE: query results group/summed by intervalnuno.tavares1 Aug
    • RE: query results group/summed by intervalTravis Ard2 Aug