Re: surely an easy quick one
Use the DISTINCT keyword and count the returned rows - Original Message - From: Jamie Burns [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, September 14, 2001 9:08 AM Subject: surely an easy quick one Hello again :o) This is surely an easy quick one but my brain wont work! I have a table (ref, name, team, seminar_ref) like: 1billsupport100 2billsupport101 3billsupport102 4ben development 201 5ben development 103 6ben development 204 7bob support 104 8bob support102 9bob support107 7pat support 102 8pat support 202 9pat support 105 I need to get a count of how many individual people are in the table, that belong to a certain group. So if I wanted to find all individual people in support the answer in this case it would be 3 (bill, bob and pat). I dont need their names, just the count. Thanks, Jamie. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: surely an easy quick one
I suggest: select count(*) from table your_table_name group by team ; The MySQL documentation at www.mysql.com certainly contains good documentation regarding the group by clause within it. However, it doesn't really provide a motivation for using it... nor should it. It is documentation regarding how MySQL implements SQL and RDMS in general, and not a general SQL tutorial or reference. I suggest you look a bit further afield for this kind of material. I got started with this stuff back oh 5-6 years ago with Teach Yourself Transact-SQL in 21 Days. (I was working with MS SQL Server 6.5 at the time.) It did a pretty good job at handling this kind of topic (group by), having clause, joins, cross-tab reports, etc. (It also did views, stored procedures, batch programming... all sorts of stuff I hope MySQL has one of these days. :-) ) I'm not saying that this is the book that you should get. But if you want to increase your SQL knowledge, you should look into getting something. Cheers, Richard From: Jamie Burns [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, September 14, 2001 9:08 AM Subject: surely an easy quick one Hello again :o) This is surely an easy quick one but my brain wont work! I have a table (ref, name, team, seminar_ref) like: 1billsupport100 2billsupport101 3billsupport102 4ben development 201 5ben development 103 6ben development 204 7bob support 104 8bob support102 9bob support107 7pat support 102 8pat support 202 9pat support 105 I need to get a count of how many individual people are in the table, that belong to a certain group. So if I wanted to find all individual people in support the answer in this case it would be 3 (bill, bob and pat). I dont need their names, just the count. -- Richard Dice ShadNet Creator * http://shadnet.shad.ca/ * [EMAIL PROTECTED] Occasional Writer, HotWired * http://www.hotwired.com/webmonkey/ squeeze the world 'til it's small enough to join us heel to toe - jesus jones - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
surely an easy quick one
Hello again :o) This is surely an easy quick one but my brain wont work! I have a table (ref, name, team, seminar_ref) like: 1billsupport100 2billsupport101 3billsupport102 4ben development 201 5ben development 103 6ben development 204 7bob support 104 8bob support102 9bob support107 7pat support 102 8pat support 202 9pat support 105 I need to get a count of how many individual people are in the table, that belong to a certain group. So if I wanted to find all individual people in support the answer in this case it would be 3 (bill, bob and pat). I dont need their names, just the count. Thanks, Jamie. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: surely an easy quick one
select count(*) from table where team='support' group by team -Ursprüngliche Nachricht- Von: Jamie Burns [mailto:[EMAIL PROTECTED]] Gesendet: Freitag, 14. September 2001 15:09 An: [EMAIL PROTECTED] Betreff: surely an easy quick one Hello again :o) This is surely an easy quick one but my brain wont work! I have a table (ref, name, team, seminar_ref) like: 1billsupport100 2billsupport101 3billsupport102 4ben development 201 5ben development 103 6ben development 204 7bob support 104 8bob support102 9bob support107 7pat support 102 8pat support 202 9pat support 105 I need to get a count of how many individual people are in the table, that belong to a certain group. So if I wanted to find all individual people in support the answer in this case it would be 3 (bill, bob and pat). I dont need their names, just the count. Thanks, Jamie. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: surely an easy quick one
I haven't tested this; I'm not an SQL guru. I just like a challenge :-) SELECT COUNT(*) FROM table WHERE team = support GROUP BY name; Lezz Giles - Original Message - From: Jamie Burns [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, September 14, 2001 9:08 AM Subject: surely an easy quick one Hello again :o) This is surely an easy quick one but my brain wont work! I have a table (ref, name, team, seminar_ref) like: 1billsupport100 2billsupport101 3billsupport102 4ben development 201 5ben development 103 6ben development 204 7bob support 104 8bob support102 9bob support107 7pat support 102 8pat support 202 9pat support 105 I need to get a count of how many individual people are in the table, that belong to a certain group. So if I wanted to find all individual people in support the answer in this case it would be 3 (bill, bob and pat). I dont need their names, just the count. Thanks, Jamie. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: surely an easy quick one
select count(*) from table where team='support' group by team I think that should be a group by name not by team. -- Colin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: surely an easy quick one
this doesnt work as i need :o( it returns a count of 9, not 3 in my example. i want to know how many *individual* people are in the database for a given 'team'. in my example there are three in 'support' (this is what i want to know), and they are each in the table three times (this is where your query gets 9 from). :o( - Original Message - From: Stefan Pinkert [EMAIL PROTECTED] To: Jamie Burns [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, September 14, 2001 2:25 PM Subject: Re: surely an easy quick one select count(*) from table where team='support' group by team -Ursprüngliche Nachricht- Von: Jamie Burns [mailto:[EMAIL PROTECTED]] Gesendet: Freitag, 14. September 2001 15:09 An: [EMAIL PROTECTED] Betreff: surely an easy quick one Hello again :o) This is surely an easy quick one but my brain wont work! I have a table (ref, name, team, seminar_ref) like: 1billsupport100 2billsupport101 3billsupport102 4ben development 201 5ben development 103 6ben development 204 7bob support 104 8bob support102 9bob support107 7pat support 102 8pat support 202 9pat support 105 I need to get a count of how many individual people are in the table, that belong to a certain group. So if I wanted to find all individual people in support the answer in this case it would be 3 (bill, bob and pat). I dont need their names, just the count. Thanks, Jamie. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: surely an easy quick one
Hi, idname group_name -- 1bill support 100 2bill support 101 3bill support 102 4bendevelopment 201 5bendevelopment 103 6bendevelopment 204 7bobsupport 104 8bobsupport 102 9bobsupport 107 7patsupport 102 8patsupport 202 9patsupport 105 I need to get a count of how many individual people are in the table, that belong to a certain group. select count(distinct name) from table where group_name = 'support'; Best Regards, Sasa - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: surely an easy quick one
this doesnt work as i need either :o( it returns 3 rows, each with a count of 3 inside. i just want to get a *single row* with the number 3 in it (for my example). :o( thanks for trying though... - Original Message - From: Lezz Giles [EMAIL PROTECTED] To: Jamie Burns [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, September 14, 2001 2:29 PM Subject: Re: surely an easy quick one I haven't tested this; I'm not an SQL guru. I just like a challenge :-) SELECT COUNT(*) FROM table WHERE team = support GROUP BY name; Lezz Giles - Original Message - From: Jamie Burns [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, September 14, 2001 9:08 AM Subject: surely an easy quick one Hello again :o) This is surely an easy quick one but my brain wont work! I have a table (ref, name, team, seminar_ref) like: 1billsupport100 2billsupport101 3billsupport102 4ben development 201 5ben development 103 6ben development 204 7bob support 104 8bob support102 9bob support107 7pat support 102 8pat support 202 9pat support 105 I need to get a count of how many individual people are in the table, that belong to a certain group. So if I wanted to find all individual people in support the answer in this case it would be 3 (bill, bob and pat). I dont need their names, just the count. Thanks, Jamie. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: surely an easy quick one
Ok, i have figured it out... SELECT COUNT(DISTINCT name) from tester WHERE team = 'support' But this doesnt work on my development box which runs an older version of mysql! Does anybody know how to get this type of functionality without the DISTINCT keyword inside count? :o) Jamie. - Original Message - From: Lezz Giles [EMAIL PROTECTED] To: Jamie Burns [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, September 14, 2001 2:29 PM Subject: Re: surely an easy quick one I haven't tested this; I'm not an SQL guru. I just like a challenge :-) SELECT COUNT(*) FROM table WHERE team = support GROUP BY name; Lezz Giles - Original Message - From: Jamie Burns [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, September 14, 2001 9:08 AM Subject: surely an easy quick one Hello again :o) This is surely an easy quick one but my brain wont work! I have a table (ref, name, team, seminar_ref) like: 1billsupport100 2billsupport101 3billsupport102 4ben development 201 5ben development 103 6ben development 204 7bob support 104 8bob support102 9bob support107 7pat support 102 8pat support 202 9pat support 105 I need to get a count of how many individual people are in the table, that belong to a certain group. So if I wanted to find all individual people in support the answer in this case it would be 3 (bill, bob and pat). I dont need their names, just the count. Thanks, Jamie. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: surely an easy quick one
this doesnt work as i need either :o( it returns 3 rows, each with a count of 3 inside. i just want to get a *single row* with the number 3 in it (for my example). You just need to add another clause to the where statement SELECT COUNT(*) FROM table WHERE team = support AND user=bob GROUP BY name; replacing bob with whover you wish to count -- Colin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: surely an easy quick one
Yup, you're right. I told you I wasn't an SQL guru :-) How are you running the query, and what do you want to do with the number? If you're calling it from Perl then you could run a variant of the query I gave and just look at how many rows there are in the result. On the other hand, I think if you have a recent MySQL (I don't, so I can't test this) you can say... SELECT COUNT(DISTINCT name) FROM table... HTH Lezz Giles - Original Message - From: Jamie Burns [EMAIL PROTECTED] To: Lezz Giles [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, September 14, 2001 9:56 AM Subject: Re: surely an easy quick one this doesnt work as i need either :o( it returns 3 rows, each with a count of 3 inside. i just want to get a *single row* with the number 3 in it (for my example). :o( thanks for trying though... - Original Message - From: Lezz Giles [EMAIL PROTECTED] To: Jamie Burns [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, September 14, 2001 2:29 PM Subject: Re: surely an easy quick one I haven't tested this; I'm not an SQL guru. I just like a challenge :-) SELECT COUNT(*) FROM table WHERE team = support GROUP BY name; Lezz Giles - Original Message - From: Jamie Burns [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, September 14, 2001 9:08 AM Subject: surely an easy quick one Hello again :o) This is surely an easy quick one but my brain wont work! I have a table (ref, name, team, seminar_ref) like: 1billsupport100 2billsupport101 3billsupport102 4ben development 201 5ben development 103 6ben development 204 7bob support 104 8bob support102 9bob support107 7pat support 102 8pat support 202 9pat support 105 I need to get a count of how many individual people are in the table, that belong to a certain group. So if I wanted to find all individual people in support the answer in this case it would be 3 (bill, bob and pat). I dont need their names, just the count. Thanks, Jamie. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: surely an easy quick one
Please ignore my last post. I had lost track of what you were trying to achieve with your query. -- Colin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re[2]: surely an easy quick one
Hi, SELECT COUNT(DISTINCT name) from tester WHERE team = 'support' Could 2 queries do the trick? insert into temptable select count(name) from tester where team = 'support' group by team; select count(*) from temptable; Best Regards, Sasa P.S. create table temptable(tempfield integer); - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: surely an easy quick one
O.k, how about this query. SELECT DISTINCT name,count(1) AS numPeople FROM tester Where team ='support' GROUP BY team Its a bit dirty and it returns an extra column, but this could be ignored -- Colin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: surely an easy quick one
If you want a count for a specific team: Select count(*) From table Where team = 'support'; If you want the counts for all the teams: Select team, count(*) From table Group By team; - Original Message - From: Jamie Burns [EMAIL PROTECTED] I have a table (ref, name, team, seminar_ref) like: 1billsupport100 2billsupport101 3billsupport102 4ben development 201 5ben development 103 6ben development 204 7bob support 104 8bob support102 9bob support107 7pat support 102 8pat support 202 9pat support 105 I need to get a count of how many individual people are in the table, that belong to a certain group. So if I wanted to find all individual people in support the answer in this case it would be 3 (bill, bob and pat). I dont need their names, just the count. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: surely an easy quick one
Woops! Nevermind. I didn't notice your data was denormalized. - Original Message - From: Chris Johnson [EMAIL PROTECTED] To: Jamie Burns [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, September 14, 2001 10:33 PM Subject: Re: surely an easy quick one If you want a count for a specific team: Select count(*) From table Where team = 'support'; If you want the counts for all the teams: Select team, count(*) From table Group By team; - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
