SQL Query help: Count()

Hi,

I'm trying to figure out a MySQL query that will count the number of occureneces of a each value in a given row from a dataset. I then need this count displayed in each row for the associated value.

Take the following table for example.

+----+-----------+---------+---------+----------+------------+----------+---------------------+
| ID | Name      | InStock | OnOrder | Reserved | Department | Category | RowUpdate           |
+----+-----------+---------+---------+----------+------------+----------+---------------------+
|  1 | Bloodshot |      10 |       5 |        1 | Popular    | Rock     | 2005-10-09 09:19:45 |
|  2 | Most      |      10 |       5 |        2 | Classical  | Opera    | 2005-10-09 09:19:45 |
|  3 | Jazz      |      17 |       4 |        3 | Popular    | Jazz     | 2005-10-09 09:19:45 |
|  4 | Class     |       9 |       4 |        4 | Classical  | Dance    | 2005-10-09 09:19:45 |
|  5 | Violin    |      24 |       2 |        5 | Classical  | General  | 2005-10-09 09:19:45 |
|  6 | Cha Cha   |      16 |       6 |        6 | Classical  | Vocal    | 2005-10-09 09:19:45 |
|  7 | Blues     |       2 |      25 |        7 | Popular    | Blues    | 2005-10-09 09:19:45 |
|  8 | Pure      |      32 |       3 |       18 | Popular    | Jazz     | 2005-10-09 09:19:45 |
|  9 | Mud       |      12 |      15 |       19 | Popular    | Country  | 2005-10-09 09:19:45 |
| 10 | The       |       5 |      20 |       11 | Popular    | New Age  | 2005-10-09 09:19:45 |
| 11 | Embrace   |      24 |      11 |       12 | Popular    | New Age  | 2005-10-09 09:19:45 |
| 12 | Magic     |      42 |      17 |       13 | Classical  | General  | 2005-10-09 09:19:45 |
| 13 | Lake      |      25 |      44 |       24 | Classical  | Dance    | 2005-10-09 09:19:45 |
| 14 | LaLala    |      20 |      10 |        5 | Classical  | Opera    | 2005-10-09 09:19:45 |
| 15 | Soul      |      15 |      30 |       16 | Popular    | Blues    | 2005-10-09 09:19:45 |
| 16 | Stages    |      42 |       0 |        7 | Popular    | Blues    | 2005-10-09 09:19:45 |
| 17 | Six       |      16 |       8 |        6 | Classical  | General  | 2005-10-09 09:19:45 |
+----+-----------+---------+---------+----------+------------+----------+---------------------+

Say I wanted to count the number of occurences of the department field for each value of department my result would look like this.

+----+-----------+---------+---------+----------+------------+--------------+----------+---------------------+
| ID | Name      | InStock | OnOrder | Reserved | Department | Depart_Count | Category | RowUpdate           |
+----+-----------+---------+---------+----------+------------+--------------+----------+---------------------+
|  1 | Bloodshot |      10 |       5 |        1 | Popular    |      9       | Rock     | 2005-10-09 09:19:45 |
   2 | Most      |      10 |       5 |        2 | Classical  |      8       | Opera    | 2005-10-09 09:19:45 |
|  3 | Jazz      |      17 |       4 |        3 | Popular    |      9       | Jazz     | 2005-10-09 09:19:45 |
|  4 | Class     |       9 |       4 |        4 | Classical  |      8       | Dance    | 2005-10-09 09:19:45 |
|  5 | Violin    |      24 |       2 |        5 | Classical  |      8       | General  | 2005-10-09 09:19:45 |
|  6 | Cha Cha   |      16 |       6 |        6 | Classical  |      8       | Vocal    | 2005-10-09 09:19:45 |
|  7 | Blues     |       2 |      25 |        7 | Popular    |      9       | Blues    | 2005-10-09 09:19:45 |
|  8 | Pure      |      32 |       3 |       18 | Popular    |      9       | Jazz     | 2005-10-09 09:19:45 |
|  9 | Mud       |      12 |      15 |       19 | Popular    |      9       | Country  | 2005-10-09 09:19:45 |
| 10 | The       |       5 |      20 |       11 | Popular    |      9       | New Age  | 2005-10-09 09:19:45 |
| 11 | Embrace   |      24 |      11 |       12 | Popular    |      9       | New Age  | 2005-10-09 09:19:45 |
| 12 | Magic     |      42 |      17 |       13 | Classical  |      8       | General  | 2005-10-09 09:19:45 |
| 13 | Lake      |      25 |      44 |       24 | Classical  |      8       | Dance    | 2005-10-09 09:19:45 |
| 14 | LaLala    |      20 |      10 |        5 | Classical  |      8       | Opera    | 2005-10-09 09:19:45 |
| 15 | Soul      |      15 |      30 |       16 | Popular    |      9       | Blues    | 2005-10-09 09:19:45 |
| 16 | Stages    |      42 |       0 |        7 | Popular    |      9       | Blues    | 2005-10-09 09:19:45 |
| 17 | Six       |      16 |       8 |        6 | Classical  |      8       | General  | 2005-10-09 09:19:45 |
+----+-----------+---------+---------+----------+------------+--------------+----------+---------------------+

I've managed to get the proper count result using GRIUP BY but that will only return one row for each department. I've also tried using a sub query which works on its own but not as a sub query because it returns more than one row.

Any advice greatly appreciated.

Thanks.

Table taken from: http://www.java2s.com/Code/SQL/Select-Clause/UseCOUNTGROUPandHAVING.htm