GROUP BY multiple columns

Question

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 >= p_startdate and udatestamp <= 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 >= p_startdate and udatestamp <= 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 >= p_startdate and udatestamp <= 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 >= p_startdate and udatestamp <= p_enddate
            group by year(datestamp), trans_type, trans_status;
    end case;
END

Answer 1

Personally, I hate having a CASE in the middle ofSQL. It looks and feels clumsy.

You should think of this in terms of Dynamic SQL.

Perhaps something like this

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
    SET @p_inc = p_increment;
    SET @p_inclist = 'days,months,years';
    SET @ndx = FIND_IN_SET(@p_inc,@p_inclist);
    SET @groupby =
        IF(
            @ndx=0,'datestamp',
            ELT(
                @ndx,
                'datestamp',
                'DATE_FORMAT(datestamp,''%Y-%m-01'')',
                'DATE_FORMAT(datestamp,''%Y'')'
            )
        );
    SET @sql = 'select datestamp,trans_type,trans_status,';
    SET @sql = CONCAT(@sql,'sum(base_amount) ''total_amount'',');
    SET @sql = CONCAT(@sql,'sum(trans_fees) ''total_fees'',');
    SET @sql = CONCAT(@sql,'sum(volume) ''total_volume'' ');
    SET @sql = CONCAT(@sql,'from channel_stats where ');
    SET @sql = CONCAT(@sql,'channel_id = p_channel_id and ');
    SET @sql = CONCAT(@sql,'udatestamp >= p_startdate and ');
    SET @sql = CONCAT(@sql,'udatestamp <= p_enddate ');
    SET @sql = CONCAT(@sql,'group by ',@groupby);
    SET @sql = CONCAT(@sql,',trans_type, trans_status');
    PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
END

If p_increment is not days, months, or years, the default is days

Answer 2

I prefer to take advantage of the 'string' format of dates: LEFT(datestamp, 4) is the same as YEAR(datestamp), etc:

mysql> 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.