Mysql problem with select 1

[morfasie]

Here is the sql code: "SELECT sum( P.Premium ) AS soweto, sum( T.Premium ) AS `NW meretele`
FROM production P, production T
WHERE P.`Agent Code`
LIKE '%GB01%' AND T.`Agent Code`
LIKE '%WW02%'"

when I execute this query it gets the sum(premium) for P but then multiplies it with the count of %WW02% agents in table production.

Has anybody ever encountered the same problem.

The sum(premium) for the first select is 1470, then the count of occurences of WW02 = 28, 1470*28 = 41160. The answer I am looking for is the 1470 but the aswen I am getting is 41160??

thanks

 

[PHV]

This is normal behaviour of the aggregate function.
Your query is a cartesian product.
Have you tried something like this ?
SELECT sum( P.Premium ) / count(T.Premium) ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[morfasie]

Hi thank you very much, what do you mean this is how it should be? what is a cartesian product?
no if I divide it it gives me 43.24 and 37.86?

is there another way to do my query so that I do get the correct answers??

 

[dnoeth]

SELECT
sum(case when Agent Code LIKE '%GB01%'
then Premium end) AS soweto,
sum(case when Agent Code LIKE '%WW02%'
then Premium end) AS "NW meretele"
FROM production

Dieter