SQL Query help: Count()

techguy

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

Seth Brundle

Where you currently have the COUNT(DEPARTMENT) you would need a subselect such as
SELECT COUNT(*) FROM MYTABLE WHERE DEPARTMENT =DEPARTMENT
Count is an aggregate function whereas you want it to work without aggregation.

techguy

Cool, that worked, cheers.

Seth Brundle

Glad it worked.
You can paypal me the invoice amount!