in reply to Conversion to SPROC

I think it can be done in a single query, but a three step operation that maybe is a little more understandable could look like this: 
create proc calculating_pop_summations_A
as
SELECT Sum_count = sum(Person_count), Sex, Aggregated_area
  INTO #tmp
  FROM Geo_postaggregated_pop_count
 GROUP BY Sex, Aggregated_area

update Geo_postaggregated_pop_count
   SET Sum_female_count = t.Sum_count
  FROM Geo_postaggregated_pop_count G
     , #tmp t
 WHERE G.Aggegated_area = t.Aggregated_area
   AND G.Sex = t.Sex
   AND G.Sex = 2

update Geo_postaggregated_pop_count
   SET Sum_male_count = t.Sum_count
  FROM Geo_postaggregated_pop_count G
     , #tmp t
 WHERE G.Aggegated_area = t.Aggregated_area
   AND G.Sex = t.Sex
   AND G.Sex = 1

[download]
Obviously untested, but I think this should produce the right results.

Michael

Replies are listed 'Best First'.
Re^2: Conversion to SPROC
by Win (Novice) on Jun 17, 2004 at 13:14 UTC
    With the exception of a spelling mistake this works very well. Thanks. If you enjoyed working out that, or for those that would like another go, I have have another bit of code along similar lines. As follows: 
    
sub calculating_pop_summations_B {

 

  my $sthZK_B = $dbh->prepare("

 SELECT DISTINCT Aggregated_area
 FROM Geo_postaggregated_pop_count;

          ") or die "Couldn't prepare query: ".$dbh->errstr;
  $sthZK_B->execute() or die "Couldn't execute query: ".$sthZK_B->errs
+tr;

  my @sthZK_B_A_array;

  while ( my @columns_B = $sthZK_B->fetchrow() ) {

    my $Aggregated_area = $columns_B[0];

 
    my $sthZK_B_A = "

 UPDATE Geo_postaggregated_pop_count
            SET Sum_of_person_count = (

 SELECT DISTINCT (SELECT SUM(G.Person_count)
            FROM Geo_postaggregated_pop_count G
            WHERE G.Sex = 1 AND G.Aggregated_area LIKE '".$Aggregated_
+area."')+
 (SELECT SUM(G.Person_count)
            FROM Geo_postaggregated_pop_count G
            WHERE G.Sex = 2 AND G.Aggregated_area LIKE '".$Aggregated_
+area."')
 FROM Geo_postaggregated_pop_count G
            WHERE G.Aggregated_area LIKE '".$Aggregated_area."')
 FROM Geo_postaggregated_pop_count G
            WHERE G.Aggregated_area LIKE '".$Aggregated_area."'


";

    push (@sthZK_B_A_array, $sthZK_B_A);

  }
  
  
  foreach (@sthZK_B_A_array){

    
    my $sthZK_C = $dbh->prepare("
             ".$_."
          ") or die "Couldn't prepare query: ".$dbh->errstr;
    $sthZK_C->execute() or die "Couldn't execute query: ".$sthZK_C->er
+rstr; 
  }


}

    [download]
[reply]
[d/l]
      True to my word. A lesson in keeping it simple I think. 
      
SELECT Sum_count = sum(Person_count), Aggregated_area
  INTO #tmp_B
  FROM Geo_postaggregated_pop_count
 GROUP BY Aggregated_area;


 UPDATE Geo_postaggregated_pop_count
   SET Sum_of_person_count = t.Sum_count
   FROM Geo_postaggregated_pop_count G
     , #tmp_B t
 WHERE G.Aggregated_area = t.Aggregated_area

      [download]
[reply]
[d/l]
      I will have a go myself also.
[reply]

In Section Seekers of Perl Wisdom