MySQL Combine multiple rows

Question

I have a table similar to the following (of course with more rows and fields):

category_id | client_id | date        | step
     1            1       2009-12-15    first_step
     1            1       2010-02-03    last_step

     1            2       2009-04-05    first_step
     1            2       2009-08-07    last_step

     2            3       2009-11-22    first_step

     3            4       2009-11-14    first_step
     3            4       2010-05-09    last_step

I would like to transform this so that I can calculate the time between the first and last steps and eventually find the average time between first and last steps, etc. Basically, I'm stumped at how to transform the above table into something like:

category_id | first_date | last_date
     1        2009-12-15   2010-02-03
     1        2009-04-05   2009-08-07
     2        2009-11-22   NULL
     3        2009-11-14   2010-05-09

Any help would be appreciated.

With OMG Ponies help, here's the solution:

SELECT t.category_id,
     MIN(t.date) AS first_date,
     CASE 
       WHEN COUNT(*) >= 2 THEN MAX(t.date)
       ELSE NULL
     END AS last_date
FROM TABLE t
GROUP BY t.category_id, t.client_id

Answer 1

a simple GROUP BY should do the trick

SELECT   category_id
         , MIN(first_date)
         , MAX(last_date)
    FROM TABLE
GROUP BY category_ID

Answer 2

Updated based on question update/clarification:

  SELECT t.category_id,
         MIN(t.date) AS first_date,
         CASE 
           WHEN MAX(t.date) = MIN(t.date) THEN NULL 
           ELSE MAX(t.date)
         END AS last_date
    FROM TABLE t
GROUP BY t.category_id, t.client_id

Answer 3

simple answer:

  SELECT fist.category_id, first.date, last.date
    FROM tableName first
    LEFT JOIN tableName last
        ON first.category_id = last.category_id
        AND first.step = 'first_step'
        AND last.step ='last_step'

You could also do the calculations in the query instead of just returning the two date values.

Answer 4

Try:

select
    category_id
    , min(date) as first_date
    , max(date) as last_date
from
    table_name
group by
    category_id
    , client_id

Answer 5

You need to do two sub queries and then join them together - something like the following

select * from (select *, date as first_date from table where step = "first_step") a left join ( select * date as last_date from table where step = "lastt_step") b on (a.category_id = b.category_id)

Enjoy!