MySQL SUM ( Query works but returns unexpected results )

Question

I am performing a MySQL Query on two tables

keyword_stats

+-------------+---------------+------+-----+-------------------+----------------+
| Field       | Type          | Null | Key | Default           | Extra          |
+-------------+---------------+------+-----+-------------------+----------------+
| id          | int(11)       | NO   | PRI | NULL              | auto_increment |
| keywordid   | int(11)       | NO   | MUL | NULL              |                |
| campaignid  | int(11)       | NO   |     | NULL              |                |
| clicks      | int(11)       | NO   | MUL | NULL              |                |
| impressions | int(11)       | NO   | MUL | NULL              |                |
| cost        | decimal(10,2) | NO   | MUL | NULL              |                |
| conversions | int(11)       | NO   | MUL | NULL              |                |
| timestamp   | timestamp     | NO   | MUL | CURRENT_TIMESTAMP |                |
| statsdate   | date          | NO   | MUL | NULL              |                |
+-------------+---------------+------+-----+-------------------+----------------+

AND

Keywords table

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| clientid   | int(11)      | NO   | MUL | NULL    |                |
| campaignid | int(11)      | NO   |     | NULL    |                |
| keywordid  | int(11)      | NO   | MUL | NULL    |                |
| text       | varchar(125) | NO   | MUL | NULL    |                |
| status     | varchar(10)  | NO   | MUL | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

With the following SQL Query

SELECT
    k.status, statsdate, 
    SUM( impressions ) AS impressions, SUM( clicks ) AS clicks, 
    SUM( conversions ) AS conversions, SUM( cost ) AS cost
    FROM keyword_stats
        LEFT JOIN
            (
                SELECT
                    text,keywordid,status
                FROM keywords
            ) AS k USING (keywordid)
    WHERE 
       campaignid = 56486451
       AND statsdate BETWEEN '2011-03-01' AND '2011-03-23'
       AND k.status = "enabled"
    GROUP BY keywordid
    ORDER BY conversions DESC, clicks DESC, impressions DESC LIMIT 0, 10

With the results of

+---------+------------+-------------+--------+-------------+-----------+
| status  | statsdate  | impressions | clicks | conversions | cost      |
+---------+------------+-------------+--------+-------------+-----------+
| enabled | 2011-03-01 |    71256166 | 242079 |        4247 | 891572.71 |
| enabled | 2011-03-01 |        1101 |     10 |           1 |     43.19 |
| enabled | 2011-03-01 |         210 |      6 |           0 |     23.40 |
| enabled | 2011-03-01 |         331 |      4 |           0 |     15.29 |
| enabled | 2011-03-01 |         672 |      3 |           0 |     11.41 |
| enabled | 2011-03-01 |         486 |      2 |           0 |      8.93 |
| enabled | 2011-03-01 |         254 |      2 |           0 |      9.48 |
| enabled | 2011-03-01 |        2201 |      1 |           0 |      2.96 |
| enabled | 2011-03-01 |         581 |      1 |           0 |      3.25 |
| enabled | 2011-03-01 |         483 |      1 |           0 |      4.39 |
+---------+------------+-------------+--------+-------------+-----------+
10 rows in set (12.12 sec)

The query runs and performs as expected, except as you may see the first result contains unexpected results, since it is impossible for any keywords to have that amount of clicks, conversions or cost as a sum total I'm trying to figure where MySQL is combining the totals to achieve that result

EDIT ADDED KEYWORDID

+------------+---------+------------+-------------+--------+-------------+-----------+
| keywordid  | status  | statsdate  | impressions | clicks | conversions | cost      |
+------------+---------+------------+-------------+--------+-------------+-----------+
| 2147483647 | enabled | 2011-03-01 |    71256166 | 242079 |        4247 | 891572.71 |
|  101936939 | enabled | 2011-03-01 |        1101 |     10 |           1 |     43.19 |
|   23039553 | enabled | 2011-03-01 |         210 |      6 |           0 |     23.40 |
|  117364874 | enabled | 2011-03-01 |         331 |      4 |           0 |     15.29 |
|   18862051 | enabled | 2011-03-01 |         672 |      3 |           0 |     11.41 |
|   16695651 | enabled | 2011-03-01 |         486 |      2 |           0 |      8.93 |
|   14690232 | enabled | 2011-03-01 |         254 |      2 |           0 |      9.48 |
|   18046691 | enabled | 2011-03-01 |        2201 |      1 |           0 |      2.96 |
|   22232901 | enabled | 2011-03-01 |         581 |      1 |           0 |      3.25 |
|   15072731 | enabled | 2011-03-01 |         483 |      1 |           0 |      4.39 |
+------------+---------+------------+-------------+--------+-------------+-----------+
10 rows in set (11.99 sec)

Answer 1

keywordid is a PRIMARY KEY in neither table.

Most probably, you have a keywordid with lots of records in both tables which results in a cross join on this keywordid.

Also note that, first, a LEFT JOIN is redundant in your query since your are filtering on k.status, and, second, MySQL is not that good in optimizing inline views.

Just use this:

SELECT  k.status, statsdate, 
        SUM( impressions ) AS impressions, SUM( clicks ) AS clicks, 
        SUM( conversions ) AS conversions, SUM( cost ) AS cost
FROM    keyword_stats
JOIN    keywords
USING   (keywordid)
WHERE   campaignid = 56486451
        AND statsdate BETWEEN '2011-03-01' AND '2011-03-23'
        AND k.status = "enabled"
GROUP BY
        keywordid
ORDER BY
        conversions DESC, clicks DESC, impressions DESC
LIMIT   0, 10

Answer 2

Do you need the information for each day? or do you need the data sum for all days?

If you want data for each day put "statsdate" in your group by expression.

Then, I thik that you don't need make a subquery. You can put the table name right in left join.

If you want the data for each "keywordid" put "keywordid" in the result fields. Status is not mandatory because always is "enabled"

Example:

SELECT
    keywordid, statsdate, 
    SUM( impressions ) AS impressions, SUM( clicks ) AS clicks, 
    SUM( conversions ) AS conversions, SUM( cost ) AS cost
    FROM keyword_stats
    LEFT JOIN keywords k USING (keywordid)
    WHERE 
       campaignid = 56486451
       AND statsdate BETWEEN '2011-03-01' AND '2011-03-23'
       AND k.status = "enabled"
    GROUP BY keywordid, statsdate
    ORDER BY conversions DESC, clicks DESC, impressions DESC LIMIT 0, 10