what about this I think this should work faster
_______________________________________________________________
SELECT count(1), build, room
FROM Jun04
WHERE dept=22
GROUP BY room, build
________________________________________________________________
$q = $dbh->prepare($aboveQuery);
$q->execute();
while (my @row=$q->fetchrow_array()){
$data{$row[2]}->{$row[1]} = $row[0];
}
$q->finish();
LRMK
----- Original Message -----
From: "Wiggins d Anconia" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, June 16, 2004 9:59 PM
Subject: RE: Counting help
> > [..]
> > > I will try and explain again what I am trying to do.
> > > I read in a file with a list of
> > > rooms....Then im trying to list the room once and then count
> > > how many records match for the select statement and only
> > > print the building once with a count in front of it..
> > >
> > > db:
> > > date build room dept
> > > ## 211 30 22
> > > ## 211 30 22
> > > ## 444 50 05
> > > ## 1544 20 22
> > > ## 1544 20 22
> > > ## 333 30 22
> > >
> > [..]
> > >
> > > My goal was to have this output:
> > > For 30 :
> > > 10BLD:211
> > > 1BLD:333
> > > For 20 :
> > > 2BLD:1544
> > >
> > > Thanks......
> > [..]
> >
> > Hi Rob,
> >
> > as Wiggins already mentioned - I guess the easiest way to solve this is
> > to let your database do the counting/grouping.
> >
> > Just send a statement like this to your database:
> >
> > SELECT count(*), build
> > FROM Jun04
> > WHERE dept=22 and room=20
> > GROUP by build
> >
>
> Yeh that is what I was thinking.
>
> If this must be done in Perl for some reason, then I think the hangup
> with respect to your code and the reason why moving the $cnt into the
> loop is because you are sorting by the date rather than by the other
> attributes, which means it is not possible to build the counts and allow
> the loop scoping to reset it at the right time.
>
> Instead I would use a data structure, probably of HoH to store the
> counts as you step through the list.
>
> Hint:
> $counts->{$room}->{$build}++;
>
> foreach my $room (keys %$counts) {
> print "Room: $room\n";
> foreach my $build (keys %{$counts->{$room}}) {
> print "\t$build: $counts->{$room}->{$build}\n";
> }
> }
>
> http://danconia.org
>
> --
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
> <http://learn.perl.org/> <http://learn.perl.org/first-response>
>
>
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
<http://learn.perl.org/> <http://learn.perl.org/first-response>
