[Dbix-class] DISTINCT group by sql

[Chris Lock]

I need to group by both to arrange the data how I want it if I only
groiup by channel then I get 13 results, what I need is the channels
grouped with the days because each datetime has multiple entries, each
one could be a channel

I could attach the first 50 entries in the DB to give you an idea of
what the data is like if that would help.  (Its really difficult to
explain data in text)

Heres the first 5 rows ; seperated
id;datetime;impressions;clicks;m_id;s_id;channel;c_id
"1";"2006-05-01 01:00:00";"15";"0";"350880";"715753";"Portal Site/Search
Engine";"1"
"2";"2006-05-01 01:00:00";"9";"0";"350881";"720056";"Travel";"1"
"3";"2006-05-01 01:00:00";"2";"0";"350884";"682976";"Travel";"1"
"4";"2006-05-01 01:00:00";"143";"0";"350884";"706936";"Travel";"1"
"5";"2006-05-01 01:00:00";"3";"0";"350885";"682976";"Travel";"1"

Hope that helps you understand why I need to group by both

------------------------------

Message: 4
Date: Thu, 29 Jun 2006 18:43:40 +0100
From: Matt S Trout <dbix-class at trout.me.uk>
Subject: Re: [Dbix-class] DISTINCT group by sql
To: dbix-class at lists.rawmode.org
Message-ID: <44A4114C.2060005 at trout.me.uk>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed

Chris Lock wrote:
> Hello everyone,
> 
> So Im doing a fairly complicated select on a DB I have, I need to
group
> the query by the fields specified in the group by below
> 
> my $results = $c->model('BrandingDB::Details')->search_literal(
>         'datetime >= ? AND datetime < ? AND campaign_id = ?',
>         ( $date_hash->{start_date}, $date_hash->{end_date},
> $c->session->{campaign_id} ),
>         {
>             select => [
>                 { DATE_FORMAT => [
>                     { DATE => 'datetime' },
>                     '"%d-%m-%Y"'
>                 ] },
>                 'channel',
>                 { FORMAT => [
>                     { SUM => 'clicks' },
>                     0
>                 ] },
>                 { FORMAT => [
>                     { SUM => 'impressions' },
>                     0
>                 ] },
>                 { FORMAT => [
>                     { SUM => 'clicks/impressions' },
>                     2
>                 ] }
>             ],
>             as       => [qw/ date channel clicks impressions ctr /],
>             group_by => [ { DATE => 'datetime' }, 'channel' ],
>             rows     => 25,
>             page     => $page
>         }
>     );
> 
> which makes this SQL
> 
> SELECT DATE_FORMAT( DATE( datetime ), "%d-%m-%Y" ), channel, FORMAT(
> SUM( clicks ), 0 ), FORMAT( SUM( impressions ), 0 ), FORMAT( SUM(
> clicks/impressions ), 2 ) FROM details me WHERE ( datetime >= ? AND
> datetime < ? AND campaign_id = ? ) GROUP BY DATE( datetime ), channel
> LIMIT 25 (`2006-5-1', `2006-5-31', `1')
> 
> I get results from that no porblem, but try to call a pager on that
> ($results->pager) or count for that matter this bit of SQL gets passed
> tol the DB and happily spat back at me
> 
> SELECT COUNT( DISTINCT( DATE( datetime ), channel ) ) FROM details me
> WHERE ( datetime >= ? AND datetime < ? AND campaign_id = ? )
> (`2006-5-1', `2006-5-31', `1')
> 
> With the following SQL error
> Operand should contain 1 column(s)
> 
> After a bit of messing about, the good bit of SQL it should produce is
> SELECT COUNT( DISTINCT( DATE( datetime ) ), channel ) FROM details me
> WHERE ( datetime >= '2006-5-1' AND datetime < '2006-5-31' AND
> campaign_id = 1 )

If the datetime is sufficient as an argument to the DISTINCT clause, why
do 
you need both in the GROUP BY?