SELECT DISTINCT accno || ' - ' || description
  FROM (
        SELECT project_id, chart_id, SUM(amount) AS bal
          FROM ((
                 SELECT me.*
                   FROM acc_trans me JOIN chart chart ON chart.id = me.chart_id
                  WHERE ((( transdate <= ? AND transdate >= ? ) AND chart.category = ? ))
               )) self
         GROUP BY project_id, chart_id
       ) q
  JOIN chart c ON q.chart_id = c.id
 WHERE bal != 0

##</code><code>##

acc_trans
     Column     |  Type   |         Modifiers
----------------+---------+----------------------------
 trans_id       | integer |
 chart_id       | integer | not null
 project_id     | integer |
 amount         | numeric |
 transdate      | date    | default ('now'::text)::date

chart:
   Column    |     Type     |                Modifiers
-------------+--------------+------------------------------------------
 id          | integer      | not null default nextval('id'::regclass)
 accno       | text         | not null
 description | text         |
 category    | character(1) |

project:
    Column     |  Type   |                Modifiers
---------------+---------+------------------------------------------
 id            | integer | not null default nextval('id'::regclass)
 projectnumber | text    |

##</code><code>##

sub accounts {
  my $self = shift;
  $self->result_source->schema->storage->dbh_do(
  sub {
    my ($storage, $dbh) = @_;
    my ($sql, @param) = @{${$self->as_query}};
    my $q1 = qq#
      SELECT project_id, chart_id, SUM(amount) AS bal
        FROM ($sql) self
       GROUP BY project_id, chart_id
    #;
    my $q2 = qq#
      SELECT DISTINCT accno || ' - ' || description
        FROM ($q1) q LEFT JOIN chart c ON q.chart_id = c.id
       WHERE bal != 0
    #;

    my $accounts = $dbh->selectcol_arrayref($q2, {}, map $$_[1], @param);
    return @$accounts;
  });
}

##</code><code>##

my $rs = $schema->resultset("AccTrans")->search(
   { transdate => { '>=', '2011-04-01', '<=', '2011-04-30' }, 'chart.category' => "I" },
   { join => "chart" }
);
say for $rs->accounts;

##</code><code>##

my $rs = $schema->resultset("AccTrans")->search(
   { transdate => { '>=', '2011-04-01', '<=', '2011-04-30' }, 'chart.category' => [ "I", "E" ] },
   { join => "chart" }
);
say for $rs->accounts;