CODEWITHSUNDEEP
mysqlgroup-by
user1856596
user1856596

Reputation: 7233

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

I get those results from a query:

Jon Doe 63741 | 26.11.2012 11:32:09
Joh Doe 63741 | 06.12.2012 10:30:03

As you see, the 1st column is equal, the dates in the 2nd column are not. The SQL looks like this:

select name, date from (
    select 
      concat(sfoa.firstname, ' ', sfoa.lastname, ' ', sfoa.postcode) as name,
      sfo.created_at as "date"
          from sales_flat_order sfo join sales_flat_order_address sfoa on sfo.entity_id = sfoa.parent_id where sfoa.address_type = 'shipping' and sfoa.lastname = 'Doe'
  ) d

Now I want to group that by name, like this:

group by name having count(name) > 1

and get the diff between the 2 dates. Is that possible? The result should be something like:

Jon Doe 63741 | 10

There can be more than 2 rows, I always want the first and the last row from the results.

Thanks!

Upvotes: 0

Views: 72

Answers (2)

Minesh
Minesh

Reputation: 2302

You can try like below: 

select name, date1,date2, datediff(date1,date2) as diff from (
    select 
    concat(sfoa.firstname, ' ', sfoa.lastname, ' ', sfoa.postcode) as name,
    sfo.created_at as "date1"
    from sales_flat_order sfo join sales_flat_order_address sfoa on sfo.entity_id = sfoa.parent_id where sfoa.address_type = 'shipping' and sfoa.lastname = 'Doe'
    ORDER BY sfo.created_at LIMIT 1

    UNION

    select 
    concat(sfoa.firstname, ' ', sfoa.lastname, ' ', sfoa.postcode) as name,
    sfo.created_at as "date2"
    from sales_flat_order sfo join sales_flat_order_address sfoa on sfo.entity_id = sfoa.parent_id where sfoa.address_type = 'shipping' and sfoa.lastname = 'Doe'
    ORDER BY sfo.created_at DESC LIMIT 1
) d

GROUP BY name 
HAVING count(name) > 1

I did not tested it.

Upvotes: 0

Achrome
Achrome

Reputation: 7821

Yes, it is possible.

select name, MAX(date) - MIN(date) as date_diff from (
select 
  concat(sfoa.firstname, ' ', sfoa.lastname, ' ', sfoa.postcode) as name,
  sfo.created_at as "date"
      from sales_flat_order sfo 
      join sales_flat_order_address sfoa 
      on sfo.entity_id = sfoa.parent_id 
      where sfoa.address_type = 'shipping' 
      and sfoa.lastname = 'Doe'
) d
group by name
having count(name) > 1

You can then format the date as you want.

Upvotes: 2

Related Questions