peppiv has asked for the wisdom of the Perl Monks concerning the following question:

I'm reading from a MySQL database using DBI/DBD. I want to be able to read the results that equal a certain query, print them, their count, their percentage to another number (not important what number it is) and print them in order by count.

Here's what I'm using (could be written better than this I'm sure):

        my $sth = $dbh->prepare('SELECT DISTINCT surveys, COUNT(*) FRO
+M neverland GROUP BY surveys ORDER BY surveys DESC'); 
        $sth->execute() or die $sth->errstr; 
        while (my @result = $sth->fetchrow_array()) {
        my $percent = (@result[1] / $nothernumber)*100;
        printf qq(<tr><td><font size="2" face="Arial">@result[0]</font
+></td><td><font size="2" face="Arial">@result[1]</font></td><td align
+="right"><font face="arial" size="2">%.1f%%</td></tr>\n),$percent;
        }

[download]

This gives me something like this:

monkeys
gorillas
bats		8
9
2		17%
25%
5%


I'd like it to return this:

gorillas
monkeys
bats		9
8
2		17%
25%
5%


ORDER BY COUNT doesn't work. I put in ORDER BY DESC just to see how far I could take it. Any ideas?

TIA

peppiv

"Lost in (cyber) Space"

Replies are listed 'Best First'.
Re: ORDER BY COUNT(*)?
by Matts (Deacon) on Mar 20, 2002 at 15:36 UTC 
    SELECT DISTINCT surveys, COUNT(*) col2
FROM neverland
GROUP BY surveys
ORDER BY col2 DESC

    [download]
    Should work, though it may need an "AS" between the COUNT(*) and the col2 (an alias for the column) depending on the RDBMS (I don't know much about MySQL I'm afraid).
[reply]
[d/l]

      In mySQL you need to use the AS keyword to name a column, ie count(*) AS col2. After that, this method works fine.

      This page is intentionally left justified.

[reply]
        No, AS keyword is optional. The above query is perfect.
[reply]

      Given your GROUP BY clause, your DISTINCT keyword is completely unnecessary. And, by the way, the second column may be equivalently specified as COUNT(*) [AS] col2 or col2=COUNT(*).

      The ORDER BY clause may alternatively be specified as ORDER BY 2 DESC or as ORDER BY count(*) DESC.

[reply]
[d/l]
[select]
Re: ORDER BY COUNT(*)?
by rdfield (Priest) on Mar 20, 2002 at 15:37 UTC
    Try:
    ORDER BY 2 DESC

    [download]

    ie column position

    rdfield

[reply]
[d/l]
Re: ORDER BY COUNT(*)?
by strat (Canon) on Mar 20, 2002 at 15:37 UTC
    The problem might be that you need a column-name for "ordering": 
    SELECT DISTINCT surveys, COUNT(*) AS x FROM neverland GROUP BY surveys
+ ORDER BY x

    [download]
    Maybe, with mysql the following might work also: 
    SELECT DISTINCT surveys, COUNT(*) FROM neverland GROUP BY surveys ORDE
+R BY 2

    [download]

    Best regards,
    perl -le "s==*F=e=>y~\*martinF~stronat~=>s~[^\w]~~g=>chop,print"

[reply]
[d/l]
[select]
Re: ORDER BY COUNT(*)?
by redsquirrel (Hermit) on Mar 20, 2002 at 16:46 UTC
    nit-picking...

    In the example code you provided above, you refer to individual array elements like this: @result[1]. If you had utilized -w you would have received this helpful error message:

    Scalar value @result[1] better written as $result[1] at FOO line 17.
    You can read more about -w in perlrun.

    --Dave

[reply]
[d/l]
[select]
      Thanks redsquirrel I'll definitely read more. However, I do have -w in my #!/usr/bin/perl -w.

      peppiv
[reply]
        However, I do have -w in my #!/usr/bin/perl -w.
        The warning is being sent to your web server error log. You'll make sysamdin types happy by writing scripts that don't fill up error logs with gobbledy-gook every time someone executes your script.

        I have found it helpful to test CGI's from the command line using this command:

        perl -cw your_script.pl
        This will not execute the script, but will display many helpful warnings.

        --Dave

[reply]
[d/l]
Re: ORDER BY COUNT(*)?
by peppiv (Curate) on Mar 20, 2002 at 15:35 UTC
    Oops. Meant to say:

    This gives me something like this:

    gorillas
    monkeys
    gophers    		9
    8
    2    		25%
    17%
    5%


[reply]
Re: ORDER BY COUNT(*)?
by peppiv (Curate) on Mar 20, 2002 at 15:59 UTC
    ORDER BY 2 DESC worked perfectly!

    Thanks everyone!

    peppiv

[reply]

Back to Seekers of Perl Wisdom