Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change
 
PerlMonks  

ORDER BY COUNT(*)?

by peppiv (Curate)
on Mar 20, 2002 at 15:31 UTC ( #153002=perlquestion: print w/replies, xml ) Need Help??

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

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

      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.

        No, AS keyword is optional. The above query is perfect.

      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.

Re: ORDER BY COUNT(*)?
by rdfield (Priest) on Mar 20, 2002 at 15:37 UTC
    Try:
    ORDER BY 2 DESC

    ie column position

    rdfield

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
    Maybe, with mysql the following might work also:
    SELECT DISTINCT surveys, COUNT(*) FROM neverland GROUP BY surveys ORDE +R BY 2

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

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

      Thanks redsquirrel I'll definitely read more. However, I do have -w in my #!/usr/bin/perl -w.

      peppiv
        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

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

    Thanks everyone!

    peppiv

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%


Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://153002]
Approved by root
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (3)
As of 2021-06-14 22:08 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    What does the "s" stand for in "perls"? (Whence perls)












    Results (66 votes). Check out past polls.

    Notices?