tweaking result set order
i am successfully using the following query to select the number of hours an
employee works on each project per month:
mysql SELECT SUM(IF(MONTH(period)=01,hours,0)) as 'Jan',
SUM(IF(MONTH(period)=02,hours,0)) as 'Feb', . . . SUM(IF(MONTH
(period)=12,hours,0)) as 'Dec' FROM log WHERE id = '12345' AND period =
'1999-07-01' AND period = date_add('1999-07-01', interval 1 year) GROUP BY
pj;
the resultset (in bad ascii) is:
+---+---+---+---+--+--+---+---+---+-
--+--+---+---+
| pj| Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep
|Oct | Nov | Dec |
+---+---+---+---+--+--+---+---+---+-
--+--+---+---+
| 00748 | 0.0 | 0.0 | 2.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
0.0 | 0.0 | 0.0 | 0.0 |
| 41857 | 121.5 | 140.5 | 180.5 | 90.0 | 74.5 | 150.0 | 0.0 | 0.0 |
0.0 |75.0 | 0.0 | 213.5 |
| 41992 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 37.5 |
0.0 | 0.0 | 0.0 | 0.0 |
| 42620 | 0.0 | 0.0 | -25.0 | 0.0 | 0.0 | 0.0 | 149.5 | 110.0 |
134.0 |69.5 | 134.5 | 143.5 |
| 57307 | 0.0 | 0.0 | 0.0 | 0.0 | 1.0 | 0.0 | 0.0 | 0.0 |
0.0 | 0.0 | 0.0 | 0.0 |
| 79811 | 0.0 | 0.0 | 0.0 | 45.0 | 81.0 | 0.0 | 0.0 | 0.0 |
0.0 | 0.0 | 0.0 | 0.0 |
| HOLID | 0.0 | 7.5 | 0.0 | 0.0 | 7.5 | 0.0 | 0.0 | 0.0 |
0.0 | 0.0 | 18.5 | 53.0 |
| OTHER | 9.5 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
16.0 | 0.0 | 0.0 | 0.0 |
| PERS | 0.0 | 9.5 | 15.0 | 15.0 | 8.5 | 15.0 | 0.0 | 0.0 |
0.0 | 0.0 | 1.5 | 0.0 |
| SICK | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
9.5 |14.5 | 0.0 | 0.0 |
| VACAT | 19.0 | 7.5 | 0.0 | 0.0 | 0.0 | 0.0 | 19.0 | 12.0 |
9.5 | 0.0 | 5.0 | 31.0 |
+---+---+---+---+--+--+---+---+---+-
--+--+---+---+
11 rows in set (0.01 sec)
you may notice that i'm making the select between the start date
'1999-07-01' and end date '2000-06-01' (one year from start date). the
problem is: i want to start the result set from the start date, in this
case, July. so the result set for one specific project might look somthing
like (mock-up):
+---+---+---+---+--+--+---+---+---+-
--+--+---+---+
| pj| Jul | Aug | Sep | Oct | Nov | Dec | Jan | Feb | Mar
|Apr | May | Jun |
+---+---+---+---+--+--+---+---+---+-
--+--+---+---+
| 00748 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
2.0 | 0.0 | 0.0 | 0.0 |
+---+---+---+---+--+--+---+---+---+-
--+--+---+---+
11 rows in set (0.01 sec)
any ideas?
~leif
**
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
RE: SELECT this IF that
Thank you all for your insight. Here's the query I'm using and it works great! mysql SELECT log.id, if (empnum.fname '', log.id, 'No Record') AS 'fname', if (empnum.lname '', log.id, 'No Record') AS 'lname', SUM(log.hours) AS 'hours' FROM log LEFT OUTER JOIN empnum ON log.id = empnum.id WHERE log.pj = '$pj' GROUP BY id; -Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 05, 2002 6:42 PM To: Forer, Leif; 'Nathan'; [EMAIL PROTECTED] Subject: Re: SELECT this IF that Leif, I'm not sure what you mean by tbl2's population is a sub-set of that in tbl1. tbl1 contains user id nums and hours each user spent working on a project. tbl2 contains user id nums and the first and last names that correspond to each user id. There are user id nums in tbl1 that do not exist in tbl2 (probably because the users are no longer work here). Hope that helps clarify. The query you suggested is not working the way I hoped it would. I just want to select the hours from tbl1 and the first and last name from tbl2 where the user id from tbl1 is the same as the user id from tbl2. If, the user id in tbl1 does not exist in tbl2 I want MySQL to return 'no record'. NATHAN- Because I'm selecting multiple user ids in the query, some of which exist in both tables, some of which do not, MySQL only returns the entries that exist in both tables with a SELECT tbl1.id, tbl1.data, tbl2.name FROM tbl1, tbl2 WHERE tbl1.id = tbl2.id; That means I'm not getting all the data from tbl1 and I need to get all the data from tbl1 whether or not the id # exists in tbl2. Hope this helps clarify. As you observe to Nathan, if the query is an INNER JOIN (explicit or implicitly) then only the intersection set will appear in the resultset, ie rows which appear in both tables. As well as that, you wanted those rows that appear in one tbl, but not in the other, to be included in the resultset - but dealt with differently. Let's take that as two steps: first off, how to get them included. This means that there are members of tbl1 that are not also members of tbl2 - this is what my earlier talk of populations was about. An OUTER JOIN will include not only members of the intersection/both tables, but members of the populations of both tables that do not have a corresponding/related row in the other table. However if one table contains 'all' the column values and the other only has values in the join-column that are a subset, there's not much point in checking for the situation where its members might not have a corresponding value/row in the other table (which also takes query response time). So if the 'master' table is on the left, and the 'subset' on the right (of the join condition it is called a LEFT OUTER JOIN - if the tables are the other way around with the 'master' on the right...well you get the idea. So run the SELECT with a LEFT OUTER JOIN between the tables. Where there is a row value on the 'left' that has no corresponding value in the table on the 'right' you should see gaps, zeros, or NULLs (varying by data type and MySQL client used). Working correctly so far? Now let's look at the second step: how to get those 'spaces' converted to instead say no record. If the subset table is able to provide a value, you want that value. If the subset table has no row/value to make the join, then you want the 'marker' text: IF( Jsubset.Word '', Jfull.Word, 'no record' ) Please substitute your own column names. This says if the column in the subset (right side) table has some value, use the value (above I've said use the one from the left table, but you could use the one from the right table just as easily if they are the same! On the other hand, if there is no string value because there is no corresponding/join row in the right table (the value is therefore NULL), SQL uses the 'else' portion of the if and plugs no record into the field as a position 'holder'. NB I checked that the code provided works before I sent it to you. Is the way it fits together/works clear now? If not, please present your implementation in SQL and list its short-comings compared to what you really want (not working is gives too little to work on!?), and illustrate with sample data. 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
SELECT this IF that
I want to select and join data from two tables if the same id exists in both tables. If the id only exists in one table I want MySQL to return a string like sorry, no records found. How can I do this? I'm guessing it's something like: mysql SELECT tbl1.this, tbl2.that FROM tbl1, tbl2 WHERE IF (tbl1.id = tbl2.id, return the data, no record); (Obviously that's not a real query). ** 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
RE: SELECT this IF that
DN- I'm not sure what you mean by tbl2's population is a sub-set of that in tbl1. tbl1 contains user id nums and hours each user spent working on a project. tbl2 contains user id nums and the first and last names that correspond to each user id. There are user id nums in tbl1 that do not exist in tbl2 (probably because the users are no longer work here). Hope that helps clarify. The query you suggested is not working the way I hoped it would. I just want to select the hours from tbl1 and the first and last name from tbl2 where the user id from tbl1 is the same as the user id from tbl2. If, the user id in tbl1 does not exist in tbl2 I want MySQL to return 'no record'. NATHAN- Because I'm selecting multiple user ids in the query, some of which exist in both tables, some of which do not, MySQL only returns the entries that exist in both tables with a SELECT tbl1.id, tbl1.data, tbl2.name FROM tbl1, tbl2 WHERE tbl1.id = tbl2.id; That means I'm not getting all the data from tbl1 and I need to get all the data from tbl1 whether or not the id # exists in tbl2. Hope this helps clarify. ** 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
RE: creating dream tables
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
if statements
I'm performing a query on 2 tables: mysql SELECT log.id, SUM (log.hours), empnum.lname FROM log, empnum WHERE log.id = '26009' AND log.id=empnum.id GROUP BY hours; Empty set (0.01 sec) The log table contains id numbers (plus a bunch of other data) but no corresponding names. The name are all stored in the empnum table. The problem is, there are instances where the id exists in the log table but not in the empnum table. In this case, MySQL returns and empty set. My question is: how can I still get MySQL to return all the info from the log table when it doesn't find a corresponding id entry in the empnum table? Can I use an if statement somehow? Here's a query of the same id number but performed on just the log table (to prove that there is an id entry in the log table but not the empnum table): mysql SELECT log.id, SUM (log.hours) FROM log WHERE log.id = '26009' GROUP BY id; +---++ | id | sum(log.hours) | +---++ | 26009 | 619.0 | +---++ 1 row in set (0.00 sec) ** 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
select distinct this and sum that
this is working: mysql select distinct pj from tmp; +---+ | pj| +---+ | 41857 | | 41992 | | 42620 | | HOLID | | OTHER | | PERS | | SICK | | VACAT | +---+ 8 rows in set (0.00 sec) now, i want to do something like this: mysql select sum(hours) from tmp where pj=distinct group by month(period); does mysql support something like this? ** 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
RE: creating dream tables
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 (-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
FW: creating dream tables
Oops. The reason I wasn't able to get past 12 had nothing to do with 12. I was saying: select sum(if(month(period)=12,hours,0)) as Dec from log group by pj; the Dec was the problem. of course, a simple renaming fixes the prob. -Original Message- From: Forer, Leif Sent: Thursday, February 28, 2002 2:19 PM To: 'DL Neil'; [EMAIL PROTECTED] Subject: RE: creating dream tables 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 (-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
group by month
There is a date column in a table and I want to select data from the table by month (instead of by every single entry within each month). Here's a sample of the table: mysql SELECT period,hours FROM log GROUP BY period; ++---+ | period| hours | ++---+ | 1999-07-15 | 73.1 | | 1999-07-31 | 82.0 | | 1999-08-15 | 36.5 | | 1999-08-31 | 78.5 | | 1999-09-15 | 55.0 | | 1999-09-30 | 52.5 | | 1999-10-15 | 56.0 | | 1999-10-31 | 61.0 | | 1999-11-15 | 7.5 | | 1999-11-30 | 26.0 | | 1999-12-15 | 17.5 | | 1999-12-31 | -12.5 | ++---+ 12 rows in set (0.00 sec) I would love to do something like select period and hours from table and group by month. Is there a MySQL function to help with this? ~Leif ** 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
RE: group by month
Boy, that was easy. I just figured it out after sending the question: mysql SELECT period,hours FROM log GROUP BY MONTH(period); -Original Message- From: Forer, Leif Sent: Tuesday, February 26, 2002 5:32 PM To: '[EMAIL PROTECTED]' Subject: group by month There is a date column in a table and I want to select data from the table by month (instead of by every single entry within each month). Here's a sample of the table: mysql SELECT period,hours FROM log GROUP BY period; ++---+ | period| hours | ++---+ | 1999-07-15 | 73.1 | | 1999-07-31 | 82.0 | | 1999-08-15 | 36.5 | | 1999-08-31 | 78.5 | | 1999-09-15 | 55.0 | | 1999-09-30 | 52.5 | | 1999-10-15 | 56.0 | | 1999-10-31 | 61.0 | | 1999-11-15 | 7.5 | | 1999-11-30 | 26.0 | | 1999-12-15 | 17.5 | | 1999-12-31 | -12.5 | ++---+ 12 rows in set (0.00 sec) I would love to do something like select period and hours from table and group by month. Is there a MySQL function to help with this? ~Leif ** 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
