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?
@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.
@nazirula : I did it before but it didn't work, I'll try again.
@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 (;