Here is my query:


SELECT tblPopTypes.attack AS attack2, tblPopTypes.defense AS defense2, tblPopTypes.magic AS magic2, COUNT( tblPop . * ) * attack2 AS a_count, COUNT( tblPop . * ) * defense2 AS d_count, COUNT( tblPop . * ) * magic2 AS m_count
FROM tblPop, tblPopTypes
WHERE tblPop.username = 'James' && tblPop.type = '2' && tblPopTypes.id = '2'


It gives me this error:

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '*)*attack2 AS a_count, COUNT(tblPop.*)*defense2 AS d_count, COU

The query works if I take all 3 COUNT()'s out of the query, but I can not see any problems with the COUNT()'s I have in there.

Someone on SitePoint got me pointed in the right direction, and after a couple changes I found I needed a GROUP BY in there.


SELECT tblPopTypes.attack AS attack2, COUNT(tblPop.type)*tblPopTypes.attack AS a_count, COUNT(tblPop.type)*tblPopTypes.defense AS d_count, COUNT(tblPop.type)*tblPopTypes.magic AS m_count FROM tblPop, tblPopTypes WHERE tblPop.username = 'James' && tblPop.type = '2' && tblPopTypes.id = '2' GROUP BY attack2