MySQL: Getting the diff between 2 dates which come from a sub select + group them by a criteria? -

MySQL: Getting the diff between 2 dates which come from a sub select + group them by a criteria? -

i results query:

jon doe 63741 | 26.11.2012 11:32:09 joh doe 63741 | 06.12.2012 10:30:03

as see, 1st column equal, dates in 2nd column not. sql looks this:

select name, date ( select concat(sfoa.firstname, ' ', sfoa.lastname, ' ', sfoa.postcode) name, sfo.created_at "date" sales_flat_order sfo bring together sales_flat_order_address sfoa on sfo.entity_id = sfoa.parent_id sfoa.address_type = 'shipping' , sfoa.lastname = 'doe' ) d

now want grouping name, this:

group name having count(name) > 1

and diff between 2 dates. possible? result should like:

jon doe 63741 | 10

there can more 2 rows, want first , lastly row results.

thanks!

yes, possible.

select name, max(date) - min(date) date_diff ( select concat(sfoa.firstname, ' ', sfoa.lastname, ' ', sfoa.postcode) name, sfo.created_at "date" sales_flat_order sfo bring together sales_flat_order_address sfoa on sfo.entity_id = sfoa.parent_id sfoa.address_type = 'shipping' , sfoa.lastname = 'doe' ) d grouping name having count(name) > 1

you can format date want.

mysql group-by