Help with query with group by and count(*)

[Albert Pinto]

<PRE>

RES table is used to store an applicants data. The columns field1 and field2 contain an id value which refers to the primary and secondary area in which the applicant has specialized in.

RES TABLE:
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| name | varchar(50)| YES | | NULL | |
| email | varchar(60)| YES | | NULL | |
| field1 | int(11) | YES | | NULL | |
| field2 | int(11) | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+

Here is the AREA table. RES.field1 and RES.field2 are foreign keys to AREA.id column.

AREA TABLE:
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | | PRI | 0 | |
| field | varchar(25) | | | | |
+-------+-------------+------+-----+---------+-------+


Now I need to write a query which will count the number of applicants according to BOTH their area of specialization (field1 and field2).

</PRE>
For example, the query

SELECT a.field, count(*)
FROM res r, area a
WHERE r.field1 = a.id
GROUP BY r.field1
ORDER BY 2 DESC

counts applicants by their primary area of specialization and produces the result:

+------------------------+----------+
| field | count(*) |
+------------------------+----------+
| Mechanical | 152 |
| Electrical/Electronics | 138 |
| Chemical | 130 |
| Civil | 31 |
| Industrial | 18 |
| Other | 15 |
| Materials | 12 |
| Semiconductor | 11 |
| Control Systems | 10 |
| Environmental | 7 |
| Aeronautical | 6 |
| Unspecified | 5 |
| Petroleum | 3 |
| Software | 3 |
| Nuclear | 1 |
| Biochemical | 1 |
+------------------------+----------+
16 rows in set (0.02 sec)

Similarly, the query:

SELECT a.field, count(*)
FROM res r, area a
WHERE r.field2 = a.id
GROUP BY r.field2
ORDER BY 2 DESC

counts applicants by their secondary area of specialization and produces the following results:

+------------------------+----------+
| field | count(*) |
+------------------------+----------+
| Unspecified | 134 |
| Other | 85 |
| Industrial | 47 |
| Control Systems | 47 |
| Electrical/Electronics | 36 |
| Mechanical | 33 |
| Petroleum | 30 |
| Semiconductor | 22 |
| Environmental | 22 |
| Materials | 22 |
| Chemical | 20 |
| Aeronautical | 14 |
| Biochemical | 12 |
| Civil | 12 |
| Nuclear | 4 |
| Software | 3 |
+------------------------+----------+
16 rows in set (0.03 sec)

But I want I really want is to count the number of applicants according to BOTH their areas of specialization count(field1) + count(field2). What is the query for doing this. Please help.