List:General Discussion« Previous MessageNext Message »
From:Albert Padley Date:July 23 2005 9:04pm
Subject:Re: Totals Across Multiple Records
View as plain text  
Sol and Peter,

Thanks for your feedback. Both of your suggestions got me going in  
the right direction and I was able to solve the problem using  
temporary tables and left joins.

Thanks again.

Albert Padley


On Jul 23, 2005, at 11:00 AM, sol beach wrote:


> create table count_temp1 select id, count(id) count_id from table ss;
> create table count_temp 2 select id, count(tt) from ss where id = sd1
> or id = sd2;
>
> should get you closer.
> yes?
>
>
> On 7/22/05, Albert Padley <apadley@stripped> 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
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:    http://lists.mysql.com/mysql? 
>> unsub=sol.beach@stripped
>>
>>
>>
>>
>
>
>
>


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