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