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
sum(CASE WHEN p_increment = 'days' THEN base_amount END ) as Total Amount Days
and so on