Here's my query so far: mysql> SELECT pj, > SUM ( hours ) AS 'Totals', > SUM( IF ( MONTH ( period ) = 01, hours, 0 )) AS 'Jan', > SUM( IF ( MONTH ( period ) = 02, hours, 0 )) AS 'Feb', > SUM( IF ( MONTH ( period ) = 03, hours, 0 )) AS 'Mar', > SUM( IF ( MONTH ( period ) = 04, hours, 0 )) AS 'Apr', > SUM( IF ( MONTH ( period ) = 05, hours, 0 )) AS 'May', > SUM( IF ( MONTH ( period ) = 06, hours, 0 )) AS 'Jun', > SUM( IF ( MONTH ( period ) = 07, hours, 0 )) AS 'Jul', > SUM( IF ( MONTH ( period ) = 08, hours, 0 )) AS 'Aug', > SUM( IF ( MONTH ( period ) = 09, hours, 0 )) AS 'Sep', > SUM( IF ( MONTH ( period ) = 10, hours, 0 )) AS 'Oct', > SUM( IF ( MONTH ( period ) = 11, hours, 0 )) AS 'Nov', > SUM( IF ( MONTH ( period ) = 12, hours, 0 )) AS 'Dec' > FROM log WHERE id = '$id' AND YEAR ( period ) >= '$sy' AND YEAR ( period ) < '$ey' GROUP BY pj;
As you can see, I'm getting data from Jan - Dec within one calendar year so there aren't problems with accidentally grabbing data from same month and different year. Also, I'm using an HTML form user to input id and select the year so there shouldn't be any problems with wrong years. I added the sum(hours) as 'totals' to get row totals for all hours per month but so far I'm using a second query to get the grand total hours. mysql> SELECT SUM ( hours ) AS 'g_total' > FROM log WHERE id='$id' AND YEAR ( period ) >= '$sy' AND YEAR ( period ) < '$ey'; Now, if you know a way that MySQL can extract the grand total from the first query I'll be able to do in one query what I originally thought I'd have to do in no less than 14! Be well. ~Leif -----Original Message----- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 28, 2002 4:26 PM To: Forer, Leif; [EMAIL PROTECTED] Subject: Re: creating dream tables Leif, > 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. Why not go for broke and make it all work in one go!? What does your current query look like, and what errmsg are you getting? When you talk about one year, it is now (let us say) March, so will the January and February 'numbers' be from this year, and all the others from last year? How do you define this? Warning: at present, if there is more than one year's data in the tbl, then there will be confusion between years. Do you have data in the tbl for every month of the year? 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
