Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be 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.

Trending News

Promoted
Kidd J
Lv 6
Kidd J asked in Computers & InternetProgramming & Design · 9 years ago

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?

Update:

@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.

Update 2:

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

Update 3:

@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

Relevance
  • nazirula
    9 years ago
    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

  • Anonymous
    9 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.

  • Motorhead
    Lv 7
    9 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?

Still have questions? Get your answers by asking now.