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