Yahoo Answers is shutting down on 4 May 2021 (Eastern Time) and the Yahoo Answers website is now in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
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 (;
3 Answers
- 9 years agoFavourite 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
- Anonymous9 years ago
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.
- MotorheadLv 79 years ago
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?
