From: Peter Brawley
Date: July 23 2005 6:14am
Subject: Re: Totals Across Multiple Records
List-Archive: http://lists.mysql.com/mysql/186849
Message-Id: <42E1E05A.6070104@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Albert,

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

SELECT
  zz,
  COUNT(zz) as zzCount,
  SUM(IF(zz=sd1 AND sd1=sd2, 1, 0)) AS sd1sd2Count
FROM ss
GROUP BY zz;

Peter Brawley
http://www.artfulsoftware.com

-----

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  
> 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
>
>


-- 
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