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), 0) as calls, n as queue_seconds from (select 0 as n union select 5 union select 10 union select 15) as step left join calls on calls.queue_seconds > (step.n - 5) and calls.queue_seconds <= step.n group by n; +-------+---------------+ | calls | queue_seconds | +-------+---------------+ | 250 | 0 | | 168 | 5 | | 268 | 10 | | 0 | 15 | +-------+---------------+ 4 rows in set (0.00 sec) -Travis ---------------------------------------- > Date: Sun, 1 Aug 2010 13:16:36 +0100 > From: nuno.tava...@dri.pt > To: mgai...@hotmail.com > CC: cuong.m...@vienthongso.com; ave...@yahoo-inc.com; mustafa...@gmail.com; > mysql@lists.mysql.com > Subject: 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-time-interval-how-many-records-are-ocurring-during-that-interval/ > > 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.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" > > > 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...@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 > > > > > > > > -- > 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=travis_...@hotmail.com > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
