mysql - group by with min max but can I do this? -
i've created query (thanks tips on stackoverflow) works great. table structure , data:
create table if not exists `transactions` ( `id` int(11) not null auto_increment, `actid` int(11) not null, `memberid` int(11) not null, `description` varchar(30) not null, `amount` decimal(10,2) not null, `date` datetime not null, `type` varchar(3) not null, primary key (`id`) ) engine=innodb default charset=utf8 auto_increment=38 ; data:
insert `transactions` (`id`, `actid`, `memberid`, `description`, `amount`, `date`, `type`) values (1, 601, 1, '', '4.00', '2008-10-31 00:00:00', 'reg'), (2, 603, 1, '', '5.00', '2010-10-26 00:00:00', 'reg'), (3, 604, 1, '', '8.00', '2011-10-21 00:00:00', 'reg'), (4, 1040, 1, '3/5', '2.00', '2009-02-18 00:00:00', 'reg'), (5, 1042, 1, '8/19', '2.00', '2010-08-19 00:00:00', 'reg'), (6, 1243, 1, '', '3.00', '2008-01-23 00:00:00', 'reg'), (7, 1301, 1, '', '1.00', '2000-10-11 00:00:00', 'reg'), (8, 1306, 1, '', '1.00', '1996-05-17 00:00:00', 'reg'), (9, 1379, 1, '', '2.00', '2007-11-01 00:00:00', 'reg'), (10, 1380, 1, '', '2.00', '2008-12-09 00:00:00', 'reg'), (11, 1381, 1, '', '2.00', '2009-10-20 00:00:00', 'reg'), (12, 1382, 1, '', '2.00', '2010-10-21 00:00:00', 'reg'), (13, 1383, 1, '', '2.00', '2011-10-20 00:00:00', 'reg'), (14, 1384, 1, 'tkt #56', '2.00', '2012-10-12 00:00:00', 'reg'), (15, 1396, 1, '', '2.00', '2006-10-12 00:00:00', 'reg'), (16, 1491, 1, '', '20.00', '2007-01-03 00:00:00', 'reg'), (17, 1494, 1, '', '40.00', '2006-01-09 00:00:00', 'reg'), (18, 1498, 1, 'guest', '32.00', '2004-01-29 00:00:00', 'reg'), (19, 601, 1, 'cash', '4.00', '2007-11-01 00:00:00', 'chk'), (20, 603, 1, 'cash', '5.00', '2009-10-26 00:00:00', 'chk'), (21, 604, 1, 'cash', '8.00', '2010-10-21 00:00:00', 'chk'), (22, 1040, 1, '2/19', '2.00', '2009-02-18 00:00:00', 'chk'), (23, 1040, 1, 'cash 3/5', '2.00', '2009-03-05 00:00:00', 'chk'), (24, 1042, 1, 'cash', '2.00', '2010-08-19 00:00:00', 'chk'), (25, 1243, 1, 'cash', '3.00', '2008-01-23 00:00:00', 'chk'), (26, 1301, 1, 'cash', '1.00', '2000-10-11 00:00:00', 'chk'), (27, 1306, 1, 'cash', '1.00', '1996-05-17 00:00:00', 'chk'), (28, 1379, 1, 'cash', '2.00', '2007-11-01 00:00:00', 'chk'), (29, 1380, 1, 'cash', '2.00', '2008-12-09 00:00:00', 'chk'), (30, 1381, 1, 'cash', '2.00', '2009-10-20 00:00:00', 'chk'), (31, 1382, 1, 'cash', '2.00', '2010-10-21 00:00:00', 'chk'), (32, 1383, 1, 'cash', '2.00', '2011-10-20 00:00:00', 'chk'), (33, 1384, 1, 'cash tkt# 56', '2.00', '2012-10-12 00:00:00', 'chk'), (34, 1396, 1, '325', '2.00', '2006-10-12 00:00:00', 'chk'), (35, 1491, 1, '', '20.00', '2007-01-03 00:00:00', 'chk'), (36, 1494, 1, '252', '40.00', '2006-01-09 00:00:00', 'chk'), (37, 1498, 1, '9223-143', '32.00', '2004-01-29 00:00:00', 'chk'); query:
select actid, sum( if( type = 'reg', amount, 0 ) ) `charge`, sum( if( type = 'chk', amount, 0 ) ) `payment`, min(date) firstdate, max(date) lastdate transactions memberid =1 group actid order firstdate results:
actid charge payment firstdate lastdate 1306 1.00 1.00 1996-05-17 00:00:00 1996-05-17 00:00:00 1301 1.00 1.00 2000-10-11 00:00:00 2000-10-11 00:00:00 1498 32.00 32.00 2004-01-29 00:00:00 2004-01-29 00:00:00 1494 40.00 40.00 2006-01-09 00:00:00 2006-01-09 00:00:00 1396 2.00 2.00 2006-10-12 00:00:00 2006-10-12 00:00:00 1491 20.00 20.00 2007-01-03 00:00:00 2007-01-03 00:00:00 601 4.00 4.00 2007-11-01 00:00:00 2008-10-31 00:00:00 1379 2.00 2.00 2007-11-01 00:00:00 2007-11-01 00:00:00 1243 3.00 3.00 2008-01-23 00:00:00 2008-01-23 00:00:00 1380 2.00 2.00 2008-12-09 00:00:00 2008-12-09 00:00:00 1040 2.00 4.00 2009-02-18 00:00:00 2009-03-05 00:00:00 1381 2.00 2.00 2009-10-20 00:00:00 2009-10-20 00:00:00 603 5.00 5.00 2009-10-26 00:00:00 2010-10-26 00:00:00 1042 2.00 2.00 2010-08-19 00:00:00 2010-08-19 00:00:00 1382 2.00 2.00 2010-10-21 00:00:00 2010-10-21 00:00:00 604 8.00 8.00 2010-10-21 00:00:00 2011-10-21 00:00:00 1383 2.00 2.00 2011-10-20 00:00:00 2011-10-20 00:00:00 1384 2.00 2.00 2012-10-12 00:00:00 2012-10-12 00:00:00 this works nicely add descriptions reg type transactions in query , cant seem work.
i think need add description select, , add description group clause
select actid, description, sum( if( type = 'reg', amount, 0 ) ) charge, sum( if( type = 'chk', amount, 0 ) ) payment, min(date) firstdate, max(date) lastdate transactions memberid =1 group actid, description order firstdate
Comments
Post a Comment