Your spec isn't entirely clear to me (eg if sd1 & sd2 were 'unique
within each record', wouldn't there be zero rows where sd1=sd2?), but is
this what you're looking for ...
COUNT(zz) as zzCount,
SUM(IF(zz=sd1 AND sd1=sd2, 1, 0)) AS sd1sd2Count
GROUP BY zz;
Albert Padley wrote:
> 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
> 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
> 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.
> Albert Padley
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.9.2/55 - Release Date: 7/21/2005