Instead using a loop to execute a set of queries, use a single GROUP BY query and loop through the results. Here's an example using our 'world' example database:
mysql> SELECT Continent, COUNT(*) AS Countries FROM country GROUP BY Continent;
+---------------+-----------+
| Continent | Countries |
+---------------+----------+
| Asia | 51 |
| Europe | 46 |
| North America | 37 |
| Africa | 58 |
| Oceania | 28 |
| Antarctica | 5 |
| South America | 14 |
+---------------+-----------+
7 rows in set (0.02 sec)
mysql> SELECT Continent, COUNT(*) AS Countries FROM country GROUP BY Continent ORDER BY Countries DESC;
+---------------+-----------+
| Continent | Countries |
+---------------+-----------+
| Africa | 58 |
| Asia | 51 |
| Europe | 46 |
| North America | 37 |
| Oceania | 28 |
| South America | 14 |
| Antarctica | 5 |
+---------------+-----------+
7 rows in set (0.00 sec)
So try these:
SELECT industry, COUNT(*) FROM jobs GROUP BY industry;
SELECT application_name, COUNT(*) FROM applications a JOIN jobs j ON j.id=a.jobID GROUP BY j.industry;
You may need to experiment with the second one in order to get exactly the results you're looking for. Since I don't know the table structure, I'm just guessing with that one. ;)
Links to MySQL documentation relating to GROUP BY can be found here:
http://dev.mysql.com/doc/mysql/search.php?q=GROUP+BY
Jon Stephens
MySQL Documentation Team @ Oracle
MySQL Dev Zone
MySQL Server Documentation
Oracle
Edited 3 time(s). Last edit at 06/09/2005 11:38PM by Jon Stephens.