Count Problem With MySQL

Stevil

I'm writing up a app which is connecting to a mysql database, I'm trying to get the COUNT of one col while getting the SUM off several other cols. I'm trying to single out a certain record using a WHERE cause, when I do this even though no values have changed its returning different results. Its a bit hard to explain so I'll just paste it here and you can see for yourself.

SELECT
count(cookie_id) as cnt_keys,
sum(action) as cnt_act,
sum(sale_count) as cnt_sale,
sum(hit_count) as cnt_hit
FROM
details_7
WHERE
keywords='freelance' AND
engine='Google'
GROUP BY
keywords

Results

cnt_keys | cnt_act | cnt_sale | cnt_hit

344 | 1 | 0 | 477

*Note: Those results are CORRECT results the next query should also display.

SELECT
count(cookie_id) as cnt_keys,
sum(action) as cnt_act,
sum(sale_count) as cnt_sale,
sum(hit_count) as cnt_hit
FROM
details_7
WHERE
engine='Google'
GROUP BY
keywords

Results

cnt_keys | cnt_act | cnt_sale | cnt_hit

315 | 1 | 0 | 432

*Note: This obviously showed many records but just comparing to the first result (These are the results that SHOULD match)

As you can see the reults differ even though I was just trying to single out that row with a where clause. I'm not sure if this is a bug or I'm over looking something (my guess is the later option). Any ideas or help people may have would be greatly appriecated, this one his me stumped.....

ultraslacker

The results do seem odd, especially as your results indicate only one value for keywords, "freelance".

Stevil

I've found the problem now I do however find it was strange how the results worked like that BUT some of the data had be entered incorrectly an some "freelance" records were actually "freelance " (note the last space) as it seems a where clause is less touchy then a group by statement. I've fixed the problem by adding a trim call onto GROUP BY trim(keywords) just odd how it ignores it in one case an not the other. Thanks for anyways 🙂