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=arch...@jab.org
Re: Need Help Writing Simple Query
Hi Mark, Please test this query: select test1.*, (select name from test2 where test2.id=test1.`v_id` limit 1) as name_1, (select name from test2 where test2.id=test1.`h_id` limit 1) as name_2 from test1; - test1 table: col1v_idh_id America 1 2 - test2 table: id name 2 SAM 1 UNCLE - Original Message - From: "Mark Phillips" To: "Mysql List" Sent: Monday, July 26, 2010 8:29:00 AM Subject: Need Help Writing Simple Query I have been away from sql for awhile, and can't seem to figure out how to write a simple query for two tables. Table 1 has many columns, two of which are hID and vID. Table 2 has two columns, ID and name. The hID and vID in table 1 correspond to the IDs in table 2. I want to make a query so I get all the columns from table 1, but substitute the names from table 2 for the hID and vID values. For example, Table 1: col 1, col 2, hID, vID, col 3 AB1 2 C Table 2: ID, name 1fred 2sam Query result: col1, col 2, hName, vName, col 3 A Bfred sam C Thanks! Mark -- 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=arch...@jab.org
Re: Table which can reference a number of other tables
Hi Marc, - If you use this database for some website, the solution is: clients (id,name) contacts (id, name, phone, client_id (FK), username, password) companies (id, name) employees (id, name, phone, company_id (FK), username, password) logins table will be removed Then you must build web interface for each group (in this example is interface for contacts and another for employees). - Another solution is: logins (id, username, password, group_type, user_id) group_type: contacts, employees, ... user_id: contact_id or employee_id If group_type is contacts, then query to the contacts table with some contact_id If group_type is employees, then query to the employees table with some employee_id - Original Message - From: "Marc Guay" To: mysql@lists.mysql.com Sent: Thursday, July 22, 2010 12:44:17 AM Subject: Table which can reference a number of other tables Hi everyone, I have a question regarding database design, I hope that this is appropriate for the list. Let's say that I have the following tables: clients (id,name) contacts (id, name, phone, client_id (FK)) companies (id, name) employees (id, name, phone, company_id (FK)) logins (id, username, password) What's the best way to connect contacts and employees to the logins table? I've thought of duplicating the username & password fields into both the contacts and employees tables, adding both contact_id and employee_id foreign keys to the logins table, and adding login_id foreign keys to the contacts and employees tables, but none of these solutions seem very smart. Thanks, Marc -- 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=arch...@jab.org
Re: why the sql so slowly?
Try Explain command before execute something Please google how to use index - Original Message - From: "hewei" To: "Ananda Kumar" Cc: mysql@lists.mysql.com Sent: Thursday, June 17, 2010 5:54:23 PM Subject: Re: why the sql so slowly? you want to select all rows from the table? ===> YES -- 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=arch...@jab.org
Re: Decimal points
Hi, Let try '' to treat numeric as character For example: select 1+'1.0'; ==> 2 select 1+'1.1'; ==> 2.1 - Original Message - From: "Mark Goodge" To: mysql@lists.mysql.com Sent: Monday, July 19, 2010 4:31:51 PM Subject: Re: Decimal points On 19/07/2010 10:04, Ashley M. Kirchner wrote: > > Is there a way to tell MySql to only return '2' in the first select as > opposed to '2.0'? The second select is correct and should remain as such. Not easily, no. > Basically I have two columns, one with an integer and another with a > decimal. And I'm adding the two, but for those where the decimal has a .0, > I just want the result to not have the .0 and for those that do have > anything other than .0, to display it accordingly. This is the sort of thing that is far better handled in the application layer, rather than the database layer. PHP, for example, even has a built-in function which will do this: setype($value,"float"); for example: => 2.1 => 2 http://www.php.net/manual/en/function.settype.php Even if other languages don't have built-in functions to do this, it's a trivial piece of code to recreate it. Mark -- http://mark.goodge.co.uk -- 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=arch...@jab.org