Re: COUNT Problem
A subselect may help: [...] Don't know ATM if it can be done more easily, but a query like this should probably work. It can be done without a sub-query: [...] That *should* work, barring any typos or ommisions I may have made. I used LEFT JOIN because of personal preference, it can be done other way(s). Ah yes, I see. Nice idea. As JOINs perform better than subselects IIRC, your solution ought to be preferred, then. Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: COUNT Problem
Hi, Tihs looks to me as too few information. What is in your tables (the information)? What does the query return (a empty set)? Maybe 'cause in your where clause where you have Project_ID 2 you should have Project_ID=2? Or you have several projects with ID2 and you want tasks for all of them? Or the query don't even run as Project_ID exists in both table and you should specify a table alias (T.Project_ID or P.Project_ID)? themage On Sunday 17 October 2004 20:36, shaun thornburgh wrote: Hi, I have the following two tables in my database: mysql DESCRIBE Projects; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | Project_ID | int(11) | | PRI | NULL| auto_increment | | Client_ID| int(11) | YES | | NULL|| | Project_Name | varchar(100) | YES | | NULL|| +--+--+--+-+-++ 3 rows in set (0.00 sec) mysql DESCRIBE Tasks; ++-+--+-+-++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-++ | Task_ID| int(11) | | PRI | NULL| auto_increment | | Task_Name | varchar(40) | | | || | Project_ID | int(11) | | | 0 || ++-+--+-+-++ 3 rows in set (0.00 sec) mysql A Project will have 1 or more tasks assigned to it. Using the following query, how can I modify it so that I can find out the largest number of tasks assigned to a group of projects. SELECT P.*, T.* FROM Projects P, Tasks T WHERE P.Project_ID = T.Project_ID AND Project_ID 2; So if Project A has 3 tasks and Project B has 4 tasks I need the query to return 4. Hope this makes sense! Thanks for your help -- Merlin, the Mage www.code.online.pt www.cultodavida.online.pt Que o(s) vosso(s) Deus(es) vos abençoe(m) E a vida vos ame e proteja -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: COUNT Problem
On Sun, 17 Oct 2004 19:36:34 +, shaun thornburgh [EMAIL PROTECTED] wrote: A Project will have 1 or more tasks assigned to it. Using the following query, how can I modify it so that I can find out the largest number of tasks assigned to a group of projects. SELECT P.*, T.* FROM Projects P, Tasks T WHERE P.Project_ID = T.Project_ID AND Project_ID 2; A subselect may help: SELECT MAX(rows) FROM (SELECT COUNT(Task_ID) AS rows FROM Tasks GROUP BY Project_ID) AS maxi; Don't know ATM if it can be done more easily, but a query like this should probably work. Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: COUNT Problem
Hi Shaun! A Project will have 1 or more tasks assigned to it. Using the following query, how can I modify it so that I can find out the largest number of tasks assigned to a group of projects. SELECT P.*, T.* FROM Projects P, Tasks T WHERE P.Project_ID = T.Project_ID AND Project_ID 2; So if Project A has 3 tasks and Project B has 4 tasks I need the query to return 4. This question is very easy to answer, but I think it's very difficult to explain to you. For the first thing, you haven't added a GROUP BY clause, which is the first thing you should do to begin with. It's strange you didn't put an GROUP BY function, and if you don't know how it works, please read a book on SQL first. Given the tables you have, this query will do the work, without subqueries: SELECT P.Project_ID, COUNT(T.Project_ID) as HowMany FROM Projects P LEFT JOIN Tasks T USING(Project_ID) GROUP BY P.Project_ID ORDER BY HowMany DESC LIMIT 0,1 Column 1 (ProjecT_ID) is the Project ID with the most assigned tasks, and Column 2 (HowMany) lists how many taks are actually asigned. It is worth pointing out two notes about the above query: - The query will return 0 as HowMany and the Project_ID for the project which has no task assigned if this is the case. I mean, thanks to the LEFT JOIN, we take into account projects with no taks attached. (This is not possible with a straigh join like yours). - Thanks to the LIMIT clause, we get only the first result, which by the way is one of the projects with the most assigned tasks. If you want to further select which one of the projects with the most assigned tasks you want, you must order by another column, like date (or filter out the projects in the WHERE clause). Given said that, Cheers, Jose Miguel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: COUNT Problem
Frederic Wenzel wrote: On Sun, 17 Oct 2004 19:36:34 +, shaun thornburgh [EMAIL PROTECTED] wrote: A Project will have 1 or more tasks assigned to it. Using the following query, how can I modify it so that I can find out the largest number of tasks assigned to a group of projects. SELECT P.*, T.* FROM Projects P, Tasks T WHERE P.Project_ID = T.Project_ID AND Project_ID 2; A subselect may help: SELECT MAX(rows) FROM (SELECT COUNT(Task_ID) AS rows FROM Tasks GROUP BY Project_ID) AS maxi; Don't know ATM if it can be done more easily, but a query like this should probably work. Fred It can be done without a sub-query: SELECT COUNT(T.Project_ID) as Yourvar FROM Projects P LEFT JOIN Tasks T USING(Project_ID) WHERE P.Project_ID 2 GROUP BY P.Project_ID ORDER BY COUNT(T.Project_ID) DESC LIMIT 1 ; That *should* work, barring any typos or ommisions I may have made. I used LEFT JOIN because of personal preference, it can be done other way(s). Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: COUNT Problem
Hi Shaun! I beg you pardon, my last message was incomplete! I will quote the last lines from my previous post: ---8- Cut here ---8--- - Thanks to the LIMIT clause, we get only the first result, which by the way is one of the projects with the most assigned tasks. If you want to further select which one of the projects with the most assigned tasks you want, you must order by another column, like date (or filter out the projects in the WHERE clause). Given said that, Cheers, Jose Miguel. ---8- Cut here ---8--- It was my intention to finish the message as: Given said that, if you want more information on how this select works, do not hesitate to ask. ;-) Again, I apologize for the confusion. Cheers, Jose Miguel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
