DN- What a thorough and thoughtful response! It works like a charm.
I'd like to generate a report based on all 12 months of the year but for some strange reason (and I'm guessing you already knew this) the method you suggested only works up to 11. Why is this? What should I do? And, it shouldn't be a big deal to just execute a second and third query that does the row totals for each line and a grand total of all the subtotals. -----Original Message----- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 28, 2002 1:16 PM To: Forer, Leif; [EMAIL PROTECTED] Subject: Re: creating dream tables 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 ************************************************************************** This e-mail and any files transmitted with it may contain privileged or confidential information. It is solely for use by the individual for whom it is intended, even if addressed incorrectly. If you received this e-mail in error, please notify the sender; do not disclose, copy, distribute, or take any action in reliance on the contents of this information; and delete it from your system. Any other use of this e-mail is prohibited. Thank you for your compliance. --------------------------------------------------------------------- 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
