how would this query be wrong?

select a.col1 as 'Column1', c.col2 as 'Column2', c.col3 as 'Column3',
count(left(b.col4,2)) as 'Column4', sum(count(left(b.col4,2))*b.col5) as 'Column5'
from t1 a, t2 b, t3 c
where a.col6=b.col7 and left(b.col4,2)=c.col2
and col8 between '2012-01-01' and '2012-01-31'
and a.col9='1' group by a.col1, c.col2 order by a.col1;

It's a MySQL 4.1 query and all I got is an error.
Error Code : 1111
Invalid use of group function
I've tried removing the 'group by' but still receiving the same error. What's wrong with the query?

2012-03-23T20:34:25Z

@Motorhed : No. I develop this query bit by bit and the group function "a.col1...." was working before. The only change I've done before receiving the error is the "sum(count(left(b.col4,2))*b.col5) as 'Column5'". I've tried removing the "sum(" function but it seems to just give me a different error. Although, I already resolve that one. I've been doing this since yesterday and I'm really out of idea and I don't know what to look for regarding this problem.

2012-03-23T20:49:56Z

@nazirula : I did it before but it didn't work, I'll try again.

2012-03-23T22:47:05Z

@Godless Heathen : Actually the query was okay before. After I add "sum(count(left(b.col4,2))*b.col5) as 'Column5'" in 'select' statement, the error comes out. I've tested it more than enough before adding the 'sum' statement. The "and left(b.col4,2)=c.col2" is not actually a math function. I'm just matching two column from two different table as a reference in order to make the combination work. Thanks anyway, I appreciate your help (;

nazirula2012-03-23T20:29:08Z

Favorite Answer

this is your problem

sum(count(left(b.col4,2))*b.col5) as 'Column5'

try remove the sum and give feedback

@Godless Heathen .. you can do the LEFT Function after where, it will not produce the error.. give it a try

Anonymous2012-03-24T03:56:02Z

Your problem is that you're trying to do math in your WHERE statement. SQL Server and Oracle both let you do that. MySQL does not.

edit: Specifically, here " and left(b.col4,2)=c.col2 "
That's your problem.

Hope that helps.

Motorhead2012-03-24T03:26:12Z

Not sure because I am not good at SQL, however, isn't a.col1 the source, and don't you group by destination? If so, then it should be "group by Column1, Column2 order by Column1" I am guessing?