#!perl
use strict;
use DBI;
use Time::Piece;
use Time::Seconds 'ONE_DAY';

my $t = Time::Piece->new();
my $t1 = $t - 6 * ONE_DAY;
my $t2 = $t + 1 * ONE_DAY;

my $dbh = DBI->connect ("dbi:CSV:", undef, undef, {
  f_dir   => "mkdir",
  f_ext   => ".csv",
  f_enc   => "utf-8",
  csv_eol => "\n",
  RaiseError => 1,
});

my $sql = "SELECT name,city, COUNT(*) AS rows 
           FROM file.csv
           WHERE (mydate >= ?) AND (mydate < ?)
           GROUP BY name,city";

my $sth = $dbh->prepare($sql);
$sth->execute($t1->ymd,$t2->ymd);

my $ar = $sth->fetchall_arrayref;
print join ",",@$_,"\n" for @$ar;
[download]

Why wouldn't this work with the code sample you posted?

my $sql = " SELECT * 
              FROM
                 ( SELECT name, city, COUNT(*) AS Rows 
                  FROM file.csv
                  WHERE ( mydate >= ? ) 
                  AND  ( mydate   < ? ) ) t1 
              INNER JOIN
                 ( SELECT name, city, COUNT(*) AS Rows 
                   FROM file.csv 
                   WHERE ( mydate >= ? )
                  AND ( mydate < ? ) ) t2 
              ON t1.mydate = t2.mydate";
[download]

From here:
http://search.cpan.org/~jzucker/SQL-Statement-1.14/lib/SQL/Statement/Syntax.pod

Docs:
Explict Join Qualifiers: NATURAL, INNER, OUTER, LEFT, RIGHT, FULL
Any thoughts?

Where does it mention support for subselects ?

If you have 2 date ranges then try

my $sql = "SELECT name, city, COUNT(*) 
           FROM file.csv
           WHERE ((mydate >= ?) AND (mydate < ?))
           OR    ((mydate >= ?) AND (mydate < ?))
           GROUP BY name,city
          ";
[download]

poj