I have been trying all kinds of join statements but I can't get the results I need.

I have 1 table that I want to query several times to extract counts of different values.

The 'trainerid' field in this table of reports links to the trainerid key in the trainers table which has first name, last name for 12 trainers.

The reports table may have 35 records marked with trainerid = 12 and 114 records marked with trainerid = 25, etc.

I want the mysql query to group together the distinct trainerid records so that I will have 12 rows representing values and field counts for these 12 trainers.

One field may have 7 different values..short text from a dropdown selector. Some records may contain 'Booked', some may contain 'Unbooked', some 'No Interest' etc, etc.

I have tried the following to retrieve 12 rows representing the activity of each trainer who has been filing reports in table "pb_appt".

After all kinds of JOINS I gave up and tried this query:

SELECT a.trainerid, COUNT(a.status),COUNT(b.status)
FROM pb_appt AS a, pb_appt AS b
WHERE a.status = 'Booked' AND b.status = 'Unbooked'

GROUP BY a.trainerid

The first column is the trainerid, the second 2 columns are supposedly counts for a.status, and b.status

12 2771 2771
18 1141 1141
50 1956 1956
58 9780 9780
69 1304 1304
77 3260 3260
79 4401 4401
80 1304 1304
84 2934 2934
88 1304 1304
89 1467 1467
93 1630 1630
143 2771 2771
144 3586 3586

It gives me 3 columns and the counts are the same in both columns all the way through all 12 trainer rows.
Whatever number shows up it shows up twice in both columns.

I know how to do this with php loops but mysql queries are tougher.

So, to condense, I am trying to query the table so that not only do I get all 12 trainers grouped by their trainerid number no matter how many reports each one has done in the pb_appt table

2) within the records linked by the same trainerid number, I want for it to do a subquery of the 'status' field and count how many distinct instances of each possible standardized text response it finds.

Such that if one trainer has 78 records and in 21 records the status field holds 'Booked',in 32 records the status field holds 'Unbooked', and 25 hold 'Not interested' I will get separate count columns for each different response in the status field.

Thanks for you kind help.