I would be grateful if those of you around this weekend could help me figure out if what I'm after is possible. I've already spent hours with the manual, the archives and my books. I've looked at JOINS and TEMP TABLES but still can't come up with a solution.

THE ENVIRONMENT: MySQL Version 4.0.24 (so subselects are not available)

THE TABLE

CREATE TABLE `ss` ( `tt` INT NOT NULL AUTO_INCREMENT , `zz` INT( 3 ) NOT NULL , `sd1` INT( 3 ) NOT NULL , `sd2` INT( 3 ) NOT NULL , PRIMARY KEY ( `id` ) );

THE ISSUE:

1. xx, sd1 and sd2 all contain id numbers. These numbers are unique within each record. 2. I need to scan the table and create a table row for each id number in zz that contains: a. how many times each id appears in zz (This is easy using COUNT) b. how many times each id appears in sd1 plus sd2. (If a total is not possible, then a separate listing for sd1 and sd2 would suffice (just like we have for zz) c. The final table should list each id number with the following columns:

id number total times id number appears in zz column total times id number appears in sd1 & sd2 combined

I sure hope this makes sense.

Oh, one more thing. I can't change the table structure because I have simplified it here for finding a solution. The above columns are part of a much larger table that is currently in use for other purposes.

Thanks.