mysql - SQL query with GROUP BY and conditions -

mysql - SQL query with GROUP BY and conditions -

i have "stock market" info table contract values. want volume (number of trades executed per day), , close price, close cost beingness lastly recorded contract cost trading day.

create table if not exists `contracts` ( `id` int(11) not null auto_increment, `price` decimal(5,2) not null, `created_at` datetime not null, primary key (`id`) ) engine=innodb default charset=latin1 auto_increment=1502 ;

i'm able info want (mostly!) doing query this:

select count(id) volume, price, date(created_at) `contracts` grouping date(created_at)

however, want close price-- cost returned cost of whatever record happens lastly id. there way lastly price value set created_at column?

one solution utilize sub-query price

select count(ctr.id) volume, (select cc.price contracts cc date(cc.created_at) = date(ctr.created_at) order cc.created_at desc limit 1) price, date(ctr.created_at) `contracts` ctr grouping date(ctr.created_at)

edit: changed query utilize limit , select price. used oder desc because row latest date.

mysql sql group