use Modern::Perl;
use DBI;

my $dbh = DBI->connect(
    "dbi:CSV:",
    "", "",
    {
        f_dir        => './DB',
        csv_sep_char => "\t",
    }
) or die "Cannot connect: $DBI::errstr";

my $sql =
'SELECT id, count(*) AS number, sum(tumor) AS tumorscore, sum(normal) AS normalscore, sum(tumor - normal) AS difference, avg(tumor - normal) AS averagedifference FROM methyl WHERE (tumor - normal) <=20 or (tumor - normal) >=20 GROUP BY id ORDER BY id';

my $sth = $dbh->prepare($sql);
$sth->execute;
while ( my $row = $sth->fetchrow_hashref ) {
    say
"id: $row->{'id'}, Count: $row->{'number'}, Score tumor: $row->{'tumorscore'}, Score normal: $row->{'normalscore'}, Difference: $row->{'difference'}, Average Difference: $row->{'averagedifference'}";
}

##</code><code>##

id: cg00381604, Count: 3, Score tumor: 99, Score normal: 174, Difference: -75, Average Difference: -25
id: cg03130891, Count: 1, Score tumor: 55, Score normal: 84, Difference: -29, Average Difference: -29
id: cg12045430, Count: 3, Score tumor: 129, Score normal: 174, Difference: -45, Average Difference: -15
id: cg13869341, Count: 1, Score tumor: 908, Score normal: 913, Difference: -5, Average Difference: -5
id: cg14008030, Count: 1, Score tumor: 688, Score normal: 776, Difference: -88, Average Difference: -88
id: cg20253340, Count: 1, Score tumor: 560, Score normal: 593, Difference: -33, Average Difference: -33
id: cg20826792, Count: 3, Score tumor: 171, Score normal: 174, Difference: -3, Average Difference: -1
id: cg21870274, Count: 1, Score tumor: 791, Score normal: 809, Difference: -18, Average Difference: -18