List:General Discussion« Previous MessageNext Message »
From:Albert Padley Date:July 23 2005 1:06am
Subject:Totals Across Multiple Records
View as plain text  
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.

Albert Padley

Thread
Totals Across Multiple RecordsAlbert Padley23 Jul
  • Re: Totals Across Multiple RecordsPeter Brawley23 Jul
Re: Totals Across Multiple RecordsAlbert Padley23 Jul