MYSQL Count not correct
===

    SELECT
    b.cID,
    b.ID,
    Count(r.userRead) AS readCount,
    COUNT(DISTINCT r.userID) AS UserCount,
    Count(c.userDownload) AS downloadCount,
    COUNT(DISTINCT c.userID) AS userDownloadCount
    FROM
     book AS b
      INNER JOIN book_event AS r ON r.bookID=s.ID AND r.bookRead = 1
      INNER JOIN book_event as c ON c.bookID=s.ID AND c.bookDownload = 1
    WHERE
     b.cID = 1011
    GROUP BY
     b.ID
    ORDER BY
     b.ID DESC

this SQL query output (count's problem)

    +-----------+-----+-----------+-----------------+--------------+-------------------+
    | cID       | ID  | readCount | UserCount       | downloadCount| userDownloadCount |
    +-----------+-----+-----------+-----------------+--------------+-------------------+
    |      1011 | 278 |      3168 |              67 |         3168 |                19 |
    |      1011 | 272 |      9918 |             122 |         9918 |                41 |
    |      1011 | 241 |     31694 |              99 |        31694 |                38 |
    +-----------+-----+-----------+-----------------+--------------+-------------------+
    3 rows in set

real value

    +-----------+-----+-----------+-----------------+--------------+-------------------+
    | cID       | ID  | readCount | UserCount       | downloadCount| userDownloadCount |
    +-----------+-----+-----------+-----------------+--------------+-------------------+
    |      1011 | 278 |       133 |              67 |           24 |                19 |
    |      1011 | 272 |       174 |             122 |           57 |                41 |
    |      1011 | 241 |       299 |              99 |          106 |                38 |
    +-----------+-----+-----------+-----------------+--------------+-------------------+

book_event (table)

    +-----+--------+----------+--------------+
    | ID  | userID | userRead | userDownload |
    +-----+--------+----------+--------------+
    | 278 |   5169 | 1        | 0            |
    | 278 |   5169 | 0        | 1            |
    | ... |   .... | .        | .            |
    | 278 |   5628 | 1        | 0            |
    | 278 |   5162 | 1        | 0            |
    +-----+--------+----------+--------------+

I need to get the count grouped on two columns. readCount and downloadCount columuns is not correct but UserCount, userDownloadCount columuns value is correct.

how can i fix this problem?


