MySQL Forums
Forum List  »  InnoDB

Help with query with group by and count(*)
Posted by: Albert Pinto
Date: May 22, 2005 08:40PM

<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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Help with query with group by and count(*)
4932
May 22, 2005 08:40PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.