Perl & MySQL - How to get top 100 most frequently used field (containing a number) from column in table?


Problem :

I have a table with ~100,000 rows.

I am trying to print to screen the most frequently used "type_num" from a column.

So if 2362 appears 1000 times in the type_num column, that being the most used type, and 1234 is used 987 times and so on, I get a desc list of most freq used type_num's.

No luck. Here is my newbie attempt:

 my $mostused = DBI->connect("$thedb","$user","$password") or die "Connection Error: $DBI::errstr\n";

  my $getfreq = $mostused->prepare(qq{SELECT `type_num` count(*) FROM `productstable` GROUP BY `type_num` ORDER BY count(*) DESC LIMIT 10}); ##just tested with 10

    $getfreq->execute() or die "Connection Error: $DBI::errstr\n";

    while(my ($type_num) = $getfreq->fetchrow_array()) {
    print qq~$type_num<br />~;
    }

    $getfreq->finish(); #not sure if correct
    $mostused->disconnect;  #not sure if correct

Connection Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'count(*) FROM `productstable` GROUP BY `type_id` ORDER BY count(*) DESC LIMIT 10' at line 1

The type_num's are a 1 to 5 digit number. (Using DBI and strict but this example is cleaned up.) I have tried several variations and examples I found while searching here but, I am doing something wrong. please help.



Solution :

Change

SELECT `type_num` count(*) 
FROM `productstable` GROUP BY `type_num` 
ORDER BY count(*) DESC LIMIT 100

To

SELECT `type_num`, count(*) as cnt 
FROM `productstable` GROUP BY `type_num` 
ORDER BY cnt DESC LIMIT 100

Comma after type_num + alias for count(*) to reuse in the ORDER BY.


Mysql Tutorials

Mysql Howto..

PHP: How to delete user without delete the 'authenticate' function

How to benchmark / test the speed of MySQL, Postgresql, MongoDB, where there are so many cache layers around?

how to search date in any format in mysql

How to quickly prune large tables?

CakePHP Sub Querys, How To?

Django: How can you stop long queries from killing your database? [closed]

How can I make my mysql database records visible to search engines ? (part 2) [closed]

How to fix MySQL error “The used SELECT statements have a different number of columns”

How to improve SQL query w/ JOINs?

How to drop composite UNIQUE KEY in MySQL?

How do I lock a table and create another in mysql

How to test connection to mysql server?

how to create database trigger on insert, update and delete operation in mysql with coldfusion?

How to SELECT data from table only recorded the last three days (use PHP, MySQL)

How to properly set encoding for files, databases, connections, etc.?

How can a not null constraint be dropped?

How to union queries from the same table in MySQL

How to update data from localhost MySQL server to remote MySQL server

How to join two tables mysql?

how to insert HTML code into a table

Show dates between now and 4 weeks later

How to get an automatically incremented column value when inserting a row into a table in mysql using java?

How do I get this MySQL script to work? [closed]

how to update several line text field in mysql php [closed]

How unsafe is actually to access MySQL directly from an App?

How to fetch NULL values in Mysql through joins?

How To assign null values in MySql using VB.NET

Fastest way to check how many posts are in a thread with MySQL and PHP

How to make a JOIN query MYSQL?

MySQL how to select on multiple tables using Not Exist