sql - MySQL - Oddities when doing a count, that I can't explain -

By doing a simple query, I get two very different results, and I'm not sure how I come to it. I am conclusion. Below are the examples given below, please tell me if I'm just looking at some of this ..

  select b.fkid as t, b.timestamp, count (b.fkid) form of hit In the form of numbers (different (BFKID)), DATE_FORMAT (BTM Stamp, '% Y-% M-% D') AS VAL 1 to A, B. WHERE a.id = B.F.Ked Group Val 1  

... Result:

  2 2009-09-25 08:33: Compared to 42 ** 27 ** 3 2009-09-25  

...:

  Select b.fkid as t, b .timestamp, count (b.fkid) as the hit, counting (separate (b.fkid)) Num, DATE_FORMAT (b.timestamp, '% Y-% m-% d') A, val1 a, b, wheré a.id = b.fkid group by t  

.. results :

2 2009-09-25 08:33:42 ** 39 ** 1 2009-09-25 3 2009-09-25 08:36:59 ** 6 ** 1 2009-09-25 10 2009-09-25 22:40:14 ** 4 ** 1 2009-09-25 I do not know 39 + 6 + 4 = 27? I expect the first value to be 49 to 27. Also tried:

  Select as BFKID T, b.timestamp, count (b.fkid) as the hit, count (b. Fkid)) As a num, from val1 to A, B. WHERE a.id = b.fkid group val1  

... which produces:

  2 2009-09-25 08:33:42 27 27 2009-09-25 from  

from below The following recommendation I tried to eliminate irrelevant data and created the query:

  Number of hits, count (differently (B.FKID)), DATE_FORMAT (b.timestamp, '% Y -% m-% d ') As val1 from A, B. WHERE a.id = b.fkid group val1  

... generated from:

  27 3 2009 -09-25  

I tried to lower it down:

  hit count as SELECT count (b.fkid), count (different ( BFKID), A, B, WHERE a.id = b.fkid group DATE_FORMAT (b.timestamp, '% Y-% m-% d'))  

... generated from this:

  27 3  

and:

  select counts . Fkid) in the form of a hit, in the form of counting (differently (B.FKID)) as a number from B. WHERE a.id = b.fkid group b.fkid  

... produced it:

  39 1 6 1 4 1  

In SQL, you should be a group of groups in all areas that are not aggregates, not just Val 1

MySQL lets you move away from not doing this (most other databases will throw an error) but this can leave you with this unexpected behavior in this way, especially if val1 is a composite produced by you Does not recognize it separately. / P>