1 files changed, 17 insertions, 10 deletions
diff --git a/perllib/FixMyStreet/App/Controller/Admin/AreaStats.pm b/perllib/FixMyStreet/App/Controller/Admin/AreaStats.pm
index 932631cba..2058ea872 100644
--- a/perllib/FixMyStreet/App/Controller/Admin/AreaStats.pm
+++ b/perllib/FixMyStreet/App/Controller/Admin/AreaStats.pm
@@ -78,6 +78,8 @@ sub stats : Private {
     my ($self, $c) = @_;
 
     my $date = DateTime->now->subtract(days => 30);
+    # set it to midnight so we get consistent result through the day
+    $date->truncate( to => 'day' );
 
     $c->forward('/admin/fetch_contacts');
 
@@ -145,21 +147,21 @@ sub stats : Private {
     my $comments = $c->model('DB::Comment')->to_body(
         $c->stash->{body}
     )->search(
-        $params,
         {
-            join     => 'problem'
+            %$params,
+            'me.id' => { 'in' => \"(select min(id) from comment where me.problem_id=comment.problem_id and problem_state not in ('', 'confirmed') group by problem_state)" },
+        },
+        {
+            join     => 'problem',
+            group_by => [ 'problem_state' ],
+            select   => [ 'problem_state', { count => 'me.id' } ],
+            as       => [ qw/problem_state state_count/ ],
         }
     );
 
-    # you can have multiple comments with the same problem state so need to only count
-    # one instance.
-    my %state_seen = ();
     while (my $comment = $comments->next) {
         my $meta_state = $state_map->{$comment->problem_state};
-        my $key = $comment->problem->id . "-$meta_state";
-        next if $state_seen{$key};
-        $c->stash->{$meta_state} += 1;
-        $state_seen{$key} = 1;
+        $c->stash->{$meta_state} += $comment->get_column('state_count');
     }
 
     $params = {
@@ -205,7 +207,12 @@ sub stats : Private {
             join     => 'problem'
         }
     )->first;
-    $c->stash->{average} = int( ($comments->get_column('time')||0)/ 60 / 60 / 24 + 0.5 );
+    my $raw_average = $comments->get_column('time');
+    if (defined $raw_average) {
+        $c->stash->{average} = int( $raw_average / 60 / 60 / 24 + 0.5 );
+    } else {
+        $c->stash->{average} = -1;
+    }
 }
 
 sub load_user_body : Private {