Posted by strawberry on 10/05/06 15:50

Gauthier wrote:
> Hello,
>
> Here is a simple sample table:
>
> mysql> SELECT * FROM test ;
> +---------+-------------+------------+-------+------+
> | id_date | id_activity | date | hours | type |
> +---------+-------------+------------+-------+------+
> | 1 | 1 | 2006-08-15 | 5 | C |
> | 2 | 1 | 2006-10-01 | 3 | P |
> | 3 | 2 | 2006-09-31 | 2 | C |
> | 4 | 2 | 2006-10-01 | 5 | P |
> +---------+-------------+------------+-------+------+
> 4 rows in set (0.00 sec)
>
> I want the total of worked hours for an activity:
>
> mysql> SELECT id_activity,date,SUM(heures) FROM test GROUP BY
> id_activity;
> +-------------+------------+------------+
> | id_activity | date | SUM(hours) |
> +-------------+------------+------------+
> | 1 | 2006-08-15 | 8 |
> | 2 | 2006-09-31 | 7 |
> +-------------+------------+------------+
> 2 rows in set (0.01 sec)
>
> Ok, that's right, but which is the 'date' selected here, the first according
> the order in the id_date column ?
>
> Now, I'd like to have the same result, but selecting the date associated
> with the type 'P'. Is it possible ?
>
> Is it also possible to do the same grouping, but displaying the first or
> the last date (not the first or last in the table, the first or last in
> the time...) ?
>
> Many thanks,
> --
> ^^ Gauthier
> (_____/°°-ç
> | \_`-"
> )/@mmm||
> \nn \nn

untested:

SELECT t1.*, t2.date
(SELECT id_activity,SUM(hours) AS total_hours FROM test GROUP BY
id_activity)t1
LEFT JOIN test t2 ON t1.id_activity = t2.id_activity WHERE t2.type =
'P';

[Back to original message]