GROUP BY multiple columns I'm trying to draw stats out of a table and depending on the increment, group them by year, month, date.

The table is as follows

CREATE TABLE `channel_stats` (
  `channel_id` int(10) unsigned DEFAULT NULL,
  `datestamp` date DEFAULT NULL,
  `udatestamp` int(10) unsigned DEFAULT NULL,
  `ip_country` char(2) DEFAULT NULL,
  `billing_country` char(2) DEFAULT NULL,
  `payment_method` varchar(45) DEFAULT NULL,
  `trans_type` varchar(45) DEFAULT NULL,
  `trans_status` varchar(45) DEFAULT NULL,
  `trans_currency` char(3) DEFAULT NULL,
  `trans_amount` decimal(13,4) unsigned DEFAULT NULL,
  `base_amount` decimal(13,4) unsigned DEFAULT NULL,
  `trans_fees` decimal(13,4) DEFAULT NULL,
  `volume` int(10) unsigned DEFAULT NULL,
  UNIQUE KEY `channel_stats_UNIQUE` (`channel_id`,`datestamp`,`ip_country`,`billing_country`,`payment_method`,`trans_type`,`trans_status`,`trans_currency`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


and the query I'm trying to use (which doesnt work) looks something like this

select 
    datestamp,
    trans_type,
    trans_status,
    sum(base_amount) 'total_amount',
    sum(trans_fees) 'total_fees',
    sum(volume) 'total_volume'
from channel_stats
where channel_id = p_channel_id
and udatestamp &gt;= p_startdate and udatestamp &lt;= p_enddate
group by case 
    when p_increment = 'days' then
        (datestamp)
    when p_increment = 'months' then
        (year(datestamp), month(datestamp))
    when p_increment = 'years' then
        (year(datestamp))
end, trans_type, trans_status;


does anyone know how i can achieve this?

EDIT:

How it would get called for each increment

call uic_get_dashboard_transactions_100('1',(unix_timestamp() - 60 * 60 * 24 * 30),unix_timestamp(),'days');
call uic_get_dashboard_transactions_100('1',(unix_timestamp() - 60 * 60 * 24 * 365),unix_timestamp(),'months');
call uic_get_dashboard_transactions_100('1',(unix_timestamp() - 60 * 60 * 24 * 365 * 5),unix_timestamp(),'years');


It works if I break it out which I think in terms of processing, is a better way of doing it, just creates more code than probably necessary. Current solution:

CREATE PROCEDURE `uic_get_stats_transactions_100`(
    p_channel_id int unsigned,
    p_startdate int unsigned,
    p_enddate int unsigned,
    p_increment varchar(10)
)
BEGIN
    case p_increment
        when 'days' then
            select 
                datestamp,
                trans_type,
                trans_status,
                sum(base_amount) 'total_amount',
                sum(trans_fees) 'total_fees',
                sum(volume) 'total_volume'
            from channel_stats
            where channel_id = p_channel_id
            and udatestamp &gt;= p_startdate and udatestamp &lt;= p_enddate
            group by datestamp, trans_type, trans_status;
        when 'months' then
            select 
                datestamp,
                trans_type,
                trans_status,
                sum(base_amount) 'total_amount',
                sum(trans_fees) 'total_fees',
                sum(volume) 'total_volume'
            from channel_stats
            where channel_id = p_channel_id
            and udatestamp &gt;= p_startdate and udatestamp &lt;= p_enddate
            group by year(datestamp), month(datestamp), trans_type, trans_status;
        when 'years' then
            select 
                datestamp,
                trans_type,
                trans_status,
                sum(base_amount) 'total_amount',
                sum(trans_fees) 'total_fees',
                sum(volume) 'total_volume'
            from channel_stats
            where channel_id = p_channel_id
            and udatestamp &gt;= p_startdate and udatestamp &lt;= p_enddate
            group by year(datestamp), trans_type, trans_status;
    end case;
END
I prefer to take advantage of the 'string' format of dates:
LEFT(datestamp, 4) is the same as YEAR(datestamp), etc:

mysql&gt; SELECT LEFT(NOW(), 4), LEFT(NOW(), 7), LEFT(NOW(), 10);
+----------------+----------------+-----------------+
| LEFT(NOW(), 4) | LEFT(NOW(), 7) | LEFT(NOW(), 10) |
+----------------+----------------+-----------------+
| 2015           | 2015-02        | 2015-02-15      |
+----------------+----------------+-----------------+


So, your complex CASE boils down to deciding between 4, 7, and 10, thereby shortening the code significantly.  (This trick works for DATE, DATETIME, TIMESTAMP, but not timestamps stored as INT.)

As a side note: It would be better to use country CHAR(2) CHARSET ascii, which takes only 2 bytes.