Leif,
This is a bit of a mind-bender, but quite easy to do:-
> i'm trying to select data from a table and format it in an html table (using
> php) but i want mysql to do as much of the hard work as possible.
I'll agree with/encourage that philosophy.
> i'm selecting data from a table with columns called 'id', 'pj', 'period',
> 'hours'.
> id: is a unique number that identifies a specific employee.
> pj: is a unique number that identifies a specific project.
> period: is a date field (yyyy-mm-dd)
My mind is rebelling at the idea that a date (a point in time) can be a period (the
length of time between two
points in time) - but then, that's me!?
> hours: is a floating int of how many hours someone worked during a
> corresponding period on a corresponding pj.
>
> here's a look at the pjs, periods, and hours for a specific employee id:
> mysql> SELECT pj, period, hours FROM log WHERE id='26393' GROUP BY hours
> ORDER BY pj, period;
> +-------+------------+-------+
> | pj | period | hours |
> +-------+------------+-------+
> | 41857 | 1999-10-31 | 75.0 |
...
> so far so good. the table i'm trying to create would look something like
> this (it's just a mock up below).
>
> | pj | July | August | September | October | November |December
> |Total |
> +-------+------+--------+-----------+---------+----------+----------+-----+
> | 41857 | 0.0 | 0.0 | 0.0 | 75.0 | 0.0 |
> 213.5 |288.5 |
> | 42620 | 149.5 | 110.0 | 134.0 | 69.5 | 134.5 | 143.5
> |741.0 |
> | ... | ... | ... | ... | ... | ...
> | ... | ... |
> | VACAT| 19.0 | 12.0 | 9.5 | 0.0 | 5.0 | 31.0
> | 76.5 |
> +-------+------+--------+-----------+---------+----------+----------+-----+
>
> the only way i can think of doing it is to make a different query for each
> pj. like:
> mysql> SELECT period, SUM(hours) FROM log WHERE pj='42620' GROUP BY
> MONTH(period);
> +------------+------------+
> | period | sum(hours) |
> +------------+------------+
> | 1999-07-31 | 149.5 |
> | 1999-08-31 | 110.0 |
> | 1999-09-30 | 134.0 |
> | 1999-10-15 | 69.5 |
> | 1999-11-30 | 134.5 |
> | 1999-12-31 | 143.5 |
> +------------+------------+
> 6 rows in set (0.00 sec)
>
> and then another one for the total column. the idea is to make a useful
> report organized by month and pj. but a challenge crops up when there is
> not an entry for a given period or month. can mysql return null or zero in
> this case? any ideas on how to make mysql do most of the work on creating
> the "dream table"?
You could improve the last query above, by changing the period column to MONTH(period)
AS TheMonth and thus
giving the column a respectable heading/label!
Let's try to amend things/take another view, so that you don't have to limit things to
one project at a time -
but work one month at a time instead. Here is a way to do it:
mysql> SELECT pj,
-> MONTH( period ) AS TheMonth,
-> sum( hours )
-> FROM project #warning: I've changed the tblNm to suit my db
-> GROUP BY pj, TheMonth;
+-------+----------+--------------+
| pj | TheMonth | sum( hours ) |
+-------+----------+--------------+
| 41857 | 10 | 75.00 |
| 41857 | 12 | 204.00 |
| 41992 | 8 | 37.50 |
| 42620 | 7 | 149.50 |
| 42620 | 8 | 100.50 |
| 42620 | 9 | 126.00 |
| 42620 | 10 | 60.50 |
| 42620 | 11 | 71.00 |
| 42620 | 12 | 12.00 |
| HOLID | 11 | 18.50 |
| HOLID | 12 | 53.00 |
| OTHER | 9 | 16.00 |
| PERS� | 11 | 0.00 |
| SICK� | 10 | 14.50 |
| VACAT | 8 | 0.00 |
| VACAT | 11 | 0.00 |
| VACAT | 12 | 31.00 |
+-------+----------+--------------+
17 rows in set (0.04 sec)
Now lets add two 'sample' columns for October and November (simply to demonstrate a
concept - but chosen because
there are multiple entries per month, per project for those months):
mysql> SELECT pj,
-> MONTH( period ) AS TheMonth,
-> SUM( hours ),
-> SUM( IF ( MONTH( period ) = 10, hours, 0 ) ) AS October,
-> SUM( IF ( MONTH( period ) = 11, hours, 0 ) ) AS November
-> FROM project
-> GROUP BY pj, TheMonth;
+-------+----------+--------------+---------+----------+
| pj | TheMonth | SUM( hours ) | October | November |
+-------+----------+--------------+---------+----------+
| 41857 | 10 | 75.00 | 75.00 | 0.00 |
| 41857 | 12 | 204.00 | 0.00 | 0.00 |
| 41992 | 8 | 37.50 | 0.00 | 0.00 |
| 42620 | 7 | 149.50 | 0.00 | 0.00 |
| 42620 | 8 | 100.50 | 0.00 | 0.00 |
| 42620 | 9 | 126.00 | 0.00 | 0.00 |
| 42620 | 10 | 60.50 | 60.50 | 0.00 |
| 42620 | 11 | 71.00 | 0.00 | 71.00 |
| 42620 | 12 | 12.00 | 0.00 | 0.00 |
| HOLID | 11 | 18.50 | 0.00 | 18.50 |
| HOLID | 12 | 53.00 | 0.00 | 0.00 |
| OTHER | 9 | 16.00 | 0.00 | 0.00 |
| PERS� | 11 | 0.00 | 0.00 | 0.00 |
| SICK� | 10 | 14.50 | 14.50 | 0.00 |
| VACAT | 8 | 0.00 | 0.00 | 0.00 |
| VACAT | 11 | 0.00 | 0.00 | 0.00 |
| VACAT | 12 | 31.00 | 0.00 | 0.00 |
+-------+----------+--------------+---------+----------+
17 rows in set (0.03 sec)
Hey the data is starting to 'appear', but it is still spread across different lines of
the table. Let's get rid
of the month-by-month approach:
mysql> SELECT pj,
-> SUM( IF ( MONTH( period ) = 10, hours, 0 ) ) AS October,
-> SUM( IF ( MONTH( period ) = 11, hours, 0 ) ) AS November
-> FROM project
-> GROUP BY pj;
+-------+---------+----------+
| pj | October | November |
+-------+---------+----------+
| 41857 | 75.00 | 0.00 |
| 41992 | 0.00 | 0.00 |
| 42620 | 60.50 | 71.00 |
| HOLID | 0.00 | 18.50 |
| OTHER | 0.00 | 0.00 |
| PERS� | 0.00 | 0.00 |
| SICK� | 14.50 | 0.00 |
| VACAT | 0.00 | 0.00 |
+-------+---------+----------+
8 rows in set (0.03 sec)
Looking good!
Now a question for you: the problem description mentions the months July through
December. What are the business
rules: will the query always feature those six months, or will it vary over time? The
answer influences how you
extend the above table's columns/months!
Oh yes, you've noticed that I conveniently ignored the 'Total' column haven't you? It
depends upon the
parameters of the query and the content of the project tbl !
Regards,
=dn
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
