MySQL(newbie) -- about a query with GROUP

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

Re: MySQL(newbie) -- about a query with GROUP

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 accord=
ing
> 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
> (_____/°°-=E7
> | \_`-"
> )/@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 =3D t2.id_activity WHERE t2.type =3D
'P';