Home > mysql, perl, Programming > Mysql count with case statement

Mysql count with case statement

In one of my recent perl scripts, I need to get a row count of total records from a mysql table, as well as the row count of some rows with some criteria. By combining the technique from http://stackoverflow.com/questions/5045124/count-case-and-when-statement-in-mysql and the atomic row fetch trick I mentioned a while back, I came up with the following script (for illustration only hence this is not the actual script that I wrote)

#!/usr/bin/perl -w
use strict;
use DBI;

# make db connection and stuff ...
my $dbh=DBI->connect( 'DBI:mysql:db:localhost:3306', 'username', 'password' ) or die( $DBI::errstr );

my $sql=qq{ select 
    count(feedback_id) as total_count, 
    count( case when feedback_type in ('suggestion', 'bug_report') then 1 else null end ) as special_count
from customer_feedbacks};

my ($total_count, $special_count)=$dbh->selectrow_array( $sql );
# do something with $total_count and $special_count

$dbh->disconnect() if $dbh;

The benefit of crafting the mysql statement this way is that I only need to do one mysql query instead of two, combined with atomic fetch, the resulting code becomes very clean.

Advertisements
Categories: mysql, perl, Programming
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: