I need to be able to do an orderby on a MySQL table that has two types of actions and hours associated with each action.
The table/data looks like this:
actions -
id|hid|act_type|hours
--+---+--------+-----
1 |32 |task-a | 5
2 |32 |task-b | 3
3 |32 |work-b | 2.8
4 |32 |work-b | 3.4
5 |33 |task-a | 3
6 |33 |work-a | 3.3
The result I eventually need is:
hid|hours_work|hours_task
---+----------+-----------
32 |6.2 |8
33 |3.3 |3
... and I'm not sure quite how to get it.
What I've got so far is this:
select a.tid,a.hours as task,b.hours as work
from actions a, actions b
where a.tid=b.tid
and a.act_type like('Task%')
and b.act_type like('Work%');
... and eventually I'd group by both a.act_type and b.act_type so that i could sum the two of them. But right now, sum-ing would return twice the hours I need, because the result set for hid 32 (directly from the query above) looks like this:
+-----+------+------+
| hid | task | work |
+-----+------+------+
| 32 | 5.00 | 3.40 |
| 32 | 5.00 | 2.80 |
| 32 | 3.00 | 3.40 |
| 32 | 3.00 | 2.80 |
+-----+------+------+
Before I can sum, this result would have to look more like this:
+-----+------+------+
| hid | task | work |
+-----+------+------+
| 32 | 5.00 | 3.40 |
| 32 | 3.00 | 2.80 |
+-----+------+------+
I'm using MySQL ver 3.23.46. I realize that there are many ways that I could set this up besides doing it in just one query, but any other way would mean re-coding half of this screen... something that many, many hours have already been invested in.
Any ideas?
The table/data looks like this:
actions -
id|hid|act_type|hours
--+---+--------+-----
1 |32 |task-a | 5
2 |32 |task-b | 3
3 |32 |work-b | 2.8
4 |32 |work-b | 3.4
5 |33 |task-a | 3
6 |33 |work-a | 3.3
The result I eventually need is:
hid|hours_work|hours_task
---+----------+-----------
32 |6.2 |8
33 |3.3 |3
... and I'm not sure quite how to get it.
What I've got so far is this:
select a.tid,a.hours as task,b.hours as work
from actions a, actions b
where a.tid=b.tid
and a.act_type like('Task%')
and b.act_type like('Work%');
... and eventually I'd group by both a.act_type and b.act_type so that i could sum the two of them. But right now, sum-ing would return twice the hours I need, because the result set for hid 32 (directly from the query above) looks like this:
+-----+------+------+
| hid | task | work |
+-----+------+------+
| 32 | 5.00 | 3.40 |
| 32 | 5.00 | 2.80 |
| 32 | 3.00 | 3.40 |
| 32 | 3.00 | 2.80 |
+-----+------+------+
Before I can sum, this result would have to look more like this:
+-----+------+------+
| hid | task | work |
+-----+------+------+
| 32 | 5.00 | 3.40 |
| 32 | 3.00 | 2.80 |
+-----+------+------+
I'm using MySQL ver 3.23.46. I realize that there are many ways that I could set this up besides doing it in just one query, but any other way would mean re-coding half of this screen... something that many, many hours have already been invested in.
Any ideas?