MySQL Forums
Forum List  »  Newbie

Re: Problem with COUNT in the statement JOIN
Posted by: irek kordirko
Date: February 25, 2012 09:30AM

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.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.