A problem with select and count function in the same query...

[avdzm]

Hey all,

 

I am having a problem with this query,

SELECT sum(tblRecipe.UNIT) as 'sumUnit', tblSpice.spiceID, spice, tblRecipe.UNIT as 'rUnit' , tblSpice.UNIT as 'sUnit', tblProduct.productID, product FROM .....

 

When I use the sum function sum(tblRecipe.UNIT) as 'sumUnit' as shown in the query above

all I get is just the sum, and not the other fields.

 

In phpadmin it works fine, except in my php page.

 

I dont understand where the problem is coming from.

[vichu.1985]

Dude,

 

Can u put the full query?

[avdzm]

Here you go,

 

$result = mysql_query("SELECT sum(tblRecipe.UNIT) as 'sumUnit', tblSpice.spiceID, spice, tblRecipe.UNIT as 'rUnit' , tblSpice.UNIT as 'sUnit', tblProduct.productID, product

                                  FROM tblRecipe, tblProduct, tblSpice

                                  WHERE tblRecipe.productID=tblProduct.productID

                                  AND tblRecipe.spiceID=tblSpice.spiceID

                                  AND tblProduct.productID='" . $productID . "';");

[vichu.1985]

Dude,

 

You try with this

 

$result = mysql_query("SELECT sum(tblRecipe.UNIT) as 'sumUnit', tblSpice.spiceID, spice, tblRecipe.UNIT as 'rUnit' , tblSpice.UNIT as 'sUnit', tblProduct.productID, product

                                  FROM tblRecipe, tblProduct, tblSpice

                                  WHERE tblRecipe.productID=tblProduct.productID

                                  AND tblRecipe.spiceID=tblSpice.spiceID

                                  AND tblProduct.productID= '$productID'");

[avdzm]

didnt work

 

[kai555]

Hi

 

when you use sum() you have and you select other columns you mysql doesn't know how to handle these other column with the sum

 

eg

Table test

val | String

1  | test

1  | test

2  | test2

 

now selectting sum(val),String .....

 

now mysql doesn't know if you want it to return 4 or if you want it to return 2 for test and 2 for test2.

 

So to solve this you have to group by the other columns

 

select sum(val),String from table group by String

 

this will give

sum | String

2    | test

2    | test2

 

 

Hope this helped