Thank you for posting create table and insert statements - it's very helpful.
Just execute your query without a count and a group by and see what it produces.
And then take an abacus or a calculator and count results yourself ;)
mysql> select *
-> FROM myfirsttablemysql x
-> JOIN mysecondtablemysql y ON y.Region = x.Region
-> WHERE (YEAR(`myDatesYYYYMMDD`)='2011')
-> order by x.id, y.region;
+-----+-----------------+--------+--------+--------+--------+--------+-------+----+
| id | myDatesYYYYMMDD | Region | Region | Hour_1 | Hour_2 | Months | Years | ID |
+-----+-----------------+--------+--------+--------+--------+--------+-------+----+
| 1 | 2011-12-21 | SOT1M | SOT1M | 8911 | 6221 | 2 | 2011 | 42 |
| 1 | 2011-12-21 | SOT1M | SOT1M | 9035 | 5822 | 1 | 2011 | 25 |
| 6 | 2011-12-13 | SOT6O | SOT6O | 6971 | 5844 | 2 | 2011 | 54 |
| 6 | 2011-12-13 | SOT6O | SOT6O | 6434 | 5492 | 1 | 2011 | 37 |
| 11 | 2011-02-22 | SOT1N | SOT1N | 8477 | 5865 | 2 | 2011 | 43 |
| 11 | 2011-02-22 | SOT1N | SOT1N | 8499 | 5384 | 1 | 2011 | 26 |
| 17 | 2011-12-19 | SOT1N | SOT1N | 8477 | 5865 | 2 | 2011 | 43 |
| 17 | 2011-12-19 | SOT1N | SOT1N | 8499 | 5384 | 1 | 2011 | 26 |
| 20 | 2011-03-01 | SOT6O | SOT6O | 6971 | 5844 | 2 | 2011 | 54 |
| 20 | 2011-03-01 | SOT6O | SOT6O | 6434 | 5492 | 1 | 2011 | 37 |
| 22 | 2011-02-26 | SOT5N | SOT5N | 18669 | 9750 | 2 | 2011 | 48 |
| 22 | 2011-02-26 | SOT5N | SOT5N | 18330 | 10123 | 1 | 2011 | 31 |
| 30 | 2011-08-19 | PMC6M | PMC6M | 18105 | 15270 | 1 | 2011 | 5 |
| 30 | 2011-08-19 | PMC6M | PMC6M | 18430 | 16607 | 2 | 2011 | 17 |
| 40 | 2011-07-06 | PMC6M | PMC6M | 18105 | 15270 | 1 | 2011 | 5 |
| 40 | 2011-07-06 | PMC6M | PMC6M | 18430 | 16607 | 2 | 2011 | 17 |
| 41 | 2011-07-06 | PMC6M | PMC6M | 18430 | 16607 | 2 | 2011 | 17 |
| 41 | 2011-07-06 | PMC6M | PMC6M | 18105 | 15270 | 1 | 2011 | 5 |
| 42 | 2011-07-11 | PMC6P | PMC6P | 13618 | 12457 | 2 | 2011 | 20 |
| 42 | 2011-07-11 | PMC6P | PMC6P | 14131 | 12154 | 1 | 2011 | 8 |
| 49 | 2011-03-15 | SOT1P | SOT1P | 5135 | 4838 | 1 | 2011 | 28 |
| 49 | 2011-03-15 | SOT1P | SOT1P | 5431 | 4965 | 2 | 2011 | 45 |
| 50 | 2011-01-24 | SOT1Q | SOT1Q | 9749 | 7475 | 2 | 2011 | 46 |
| 50 | 2011-01-24 | SOT1Q | SOT1Q | 10284 | 7045 | 1 | 2011 | 29 |
| 51 | 2011-11-10 | SOT5M | SOT5M | 8906 | 6358 | 1 | 2011 | 30 |
| 51 | 2011-11-10 | SOT5M | SOT5M | 8563 | 6754 | 2 | 2011 | 47 |
| 52 | 2011-05-05 | SOT5Q | SOT5Q | 3487 | 3769 | 1 | 2011 | 33 |
| 52 | 2011-05-05 | SOT5Q | SOT5Q | 3555 | 4036 | 2 | 2011 | 50 |
| 53 | 2011-07-07 | SOT6O | SOT6O | 6971 | 5844 | 2 | 2011 | 54 |
| 53 | 2011-07-07 | SOT6O | SOT6O | 6434 | 5492 | 1 | 2011 | 37 |
| 70 | 2011-04-04 | PMC4P | PMC4P | 12869 | 8953 | 1 | 2011 | 4 |
| 70 | 2011-04-04 | PMC4P | PMC4P | 12070 | 10221 | 2 | 2011 | 16 |
| 76 | 2011-08-20 | SOT1N | SOT1N | 8477 | 5865 | 2 | 2011 | 43 |
| 76 | 2011-08-20 | SOT1N | SOT1N | 8499 | 5384 | 1 | 2011 | 26 |
| 78 | 2011-08-23 | SOT6M | SOT6M | 9401 | 6175 | 2 | 2011 | 52 |
| 78 | 2011-08-23 | SOT6M | SOT6M | 9087 | 5938 | 1 | 2011 | 35 |
| 81 | 2011-09-05 | SOT1Q | SOT1Q | 9749 | 7475 | 2 | 2011 | 46 |
| 81 | 2011-09-05 | SOT1Q | SOT1Q | 10284 | 7045 | 1 | 2011 | 29 |
| 82 | 2011-05-23 | SOT5M | SOT5M | 8563 | 6754 | 2 | 2011 | 47 |
| 82 | 2011-05-23 | SOT5M | SOT5M | 8906 | 6358 | 1 | 2011 | 30 |
| 83 | 2011-06-27 | SOT5N | SOT5N | 18669 | 9750 | 2 | 2011 | 48 |
| 83 | 2011-06-27 | SOT5N | SOT5N | 18330 | 10123 | 1 | 2011 | 31 |
| 100 | 2011-12-22 | SOT1Q | SOT1Q | 9749 | 7475 | 2 | 2011 | 46 |
| 100 | 2011-12-22 | SOT1Q | SOT1Q | 10284 | 7045 | 1 | 2011 | 29 |
| 101 | 2011-02-15 | SOT5P | SOT5P | 6613 | 5152 | 2 | 2011 | 49 |
| 101 | 2011-02-15 | SOT5P | SOT5P | 6851 | 5136 | 1 | 2011 | 32 |
| 103 | 2011-06-13 | SOT5R | SOT5R | 8415 | 5146 | 1 | 2011 | 34 |
| 103 | 2011-06-13 | SOT5R | SOT5R | 8714 | 5847 | 2 | 2011 | 51 |
| 112 | 2011-04-12 | PMC4P | PMC4P | 12869 | 8953 | 1 | 2011 | 4 |
| 112 | 2011-04-12 | PMC4P | PMC4P | 12070 | 10221 | 2 | 2011 | 16 |
| 117 | 2011-10-04 | SOT1Q | SOT1Q | 9749 | 7475 | 2 | 2011 | 46 |
| 117 | 2011-10-04 | SOT1Q | SOT1Q | 10284 | 7045 | 1 | 2011 | 29 |
| 155 | 2011-08-22 | PMC6O | PMC6O | 12462 | 11522 | 1 | 2011 | 7 |
| 155 | 2011-08-22 | PMC6O | PMC6O | 12338 | 12210 | 2 | 2011 | 19 |
| 156 | 2011-09-06 | PMC4M | PMC4M | 10891 | 7642 | 2 | 2011 | 13 |
| 156 | 2011-09-06 | PMC4M | PMC4M | 10975 | 7996 | 1 | 2011 | 1 |
| 157 | 2011-09-26 | PMC6O | PMC6O | 12462 | 11522 | 1 | 2011 | 7 |
| 157 | 2011-09-26 | PMC6O | PMC6O | 12338 | 12210 | 2 | 2011 | 19 |
| 158 | 2011-10-16 | PMC6M | PMC6M | 18105 | 15270 | 1 | 2011 | 5 |
| 158 | 2011-10-16 | PMC6M | PMC6M | 18430 | 16607 | 2 | 2011 | 17 |
| 159 | 2011-11-30 | PMC6P | PMC6P | 13618 | 12457 | 2 | 2011 | 20 |
| 159 | 2011-11-30 | PMC6P | PMC6P | 14131 | 12154 | 1 | 2011 | 8 |
| 160 | 2011-11-30 | PMC6O | PMC6O | 12462 | 11522 | 1 | 2011 | 7 |
| 160 | 2011-11-30 | PMC6O | PMC6O | 12338 | 12210 | 2 | 2011 | 19 |
+-----+-----------------+--------+--------+--------+--------+--------+-------+----+
64 rows in set (0.00 sec)
The query groups records by y.years.
How many distinct values are in this column ?
One value - '2011'
Therefore we get only 1 output row.
How many records the query produces ?
Answer - 64.
Therefore count(*) gives 64.
Please explain what is a purpose of this query - what is a business requirement ?
It is not clear for me.
Edited 2 time(s). Last edit at 02/25/2012 03:42PM by irek kordirko.