OBS: Sorry! I don't speak english very well!

I have this SQL: ( IN THE LINE QUANTIDADE HAVE A ERROR: EXCEDIDO IS NON-AGGREGATED COLUMN )
How I can do this line, I need a non-aggregated field EXCEDIDO > 0 for each registry, in same sql?   THANKS!

SELECT ID_AREA,
            ID_OPERACAO,
            CAP.LIMITE,
            IF((AVG(DURACAO) - AVG(TRANSPORTE)) < 0 THEN 0 ELSE (AVG(DURACAO) - AVG(TRANSPORTE))) TRABALHO,
            CAP.TRANSPORTE,AVG(DURACAO) TOTAL,
            COUNT(EXCEDIDO) TOTALREG,
            IF(EXCEDIDO > 0 THEN COUNT(EXCEDIDO) ELSE NULL) QUANTIDADE,    <= THIS LINE HAVE A ERROR
            IF(AVG(DURACAO) > AVG(LIMITE) THEN SUM(EXCEDIDO) ELSE NULL) MINUTOS,
            C.DESCRICAO

FROM OPERACOES O

LEFT OUTER JOIN PROCESSADOS P ON (O.ID_OPERACAO = P.OPERACAO_ID AND AREA_ID = 2)
LEFT OUTER JOIN CAPTURAR CAP ON (O.ID_AREA = CAP.ID_CELULA) AND (O.ID_OPERACAO = CAP.ID_OPERACAO)
LEFT OUTER JOIN CELULAS C ON (O.ID_AREA = C.ID)

WHERE O.ID_AREA = 2

GROUP BY ID_OPERACAO ORDER BY ID_OPERACAO


THANKS!

"Roger Oliveira - RS/Brasil" <rogerhp@ibest.com.br> wrote in message
news:E7EECD12-27D0-4A6D-B887-FD8379B9C54A@news.elevatesoft.com...
>
> OBS: Sorry! I don't speak english very well!
>

IF(EXCEDIDO > 0 THEN COUNT(EXCEDIDO) ELSE NULL) QUANTIDADE,

should be

count(if(excedido > 0 then excedido else 0)) quantidade

but unless I'm missing something here, I don't see how this line can work
either

IF((AVG(DURACAO) - AVG(TRANSPORTE)) < 0 THEN 0 ELSE (AVG(DURACAO) -
AVG(TRANSPORTE))) TRABALHO,


seems to me you should get rid of the AVGs.

Anyway, if after you fix the other line the SQL runs, please post it again
here for others to take a look at. There could be a parsing type problem.

Robert

> I have this SQL: ( IN THE LINE QUANTIDADE HAVE A ERROR: EXCEDIDO IS
> NON-AGGREGATED COLUMN )
> How I can do this line, I need a non-aggregated field EXCEDIDO > 0 for
> each registry, in same sql?   THANKS!
>
> SELECT ID_AREA,
>             ID_OPERACAO,
>             CAP.LIMITE,
>             IF((AVG(DURACAO) - AVG(TRANSPORTE)) < 0 THEN 0 ELSE
> (AVG(DURACAO) - AVG(TRANSPORTE))) TRABALHO,
>             CAP.TRANSPORTE,AVG(DURACAO) TOTAL,
>             COUNT(EXCEDIDO) TOTALREG,
>             IF(EXCEDIDO > 0 THEN COUNT(EXCEDIDO) ELSE NULL) QUANTIDADE,
> <= THIS LINE HAVE A ERROR
>             IF(AVG(DURACAO) > AVG(LIMITE) THEN SUM(EXCEDIDO) ELSE NULL)
> MINUTOS,
>             C.DESCRICAO
>
> FROM OPERACOES O
>
> LEFT OUTER JOIN PROCESSADOS P ON (O.ID_OPERACAO = P.OPERACAO_ID AND
> AREA_ID = 2)
> LEFT OUTER JOIN CAPTURAR CAP ON (O.ID_AREA = CAP.ID_CELULA) AND
> (O.ID_OPERACAO = CAP.ID_OPERACAO)
> LEFT OUTER JOIN CELULAS C ON (O.ID_AREA = C.ID)
>
> WHERE O.ID_AREA = 2
>
> GROUP BY ID_OPERACAO ORDER BY ID_OPERACAO
>
>
> THANKS!
>
>

Roger,

<< I have this SQL: ( IN THE LINE QUANTIDADE HAVE A ERROR: EXCEDIDO IS
NON-AGGREGATED COLUMN ) >>

Robert is correct - rewrite the SELECT expression as he indicated and it
will work.

--
Tim Young
Elevate Software
www.elevatesoft.com

But, this sql don't result what I want, agregate field, dont count just excedido > 0 but all excedido all registry, I need just where excedido > 0.

thanks!

"Roger Oliveira - RS/Brasil" <rogerhp@ibest.com.br> wrote in message
news:5655EFC3-42A3-4101-9C56-C29BC98C5A9B@news.elevatesoft.com...
>
> But, this sql don't result what I want, agregate field, dont count just
> excedido > 0 but all excedido all registry, I need just where excedido >
> 0.
>

Yeah, both your and my SQL were wrong. Should not use count.

Total excedido > 0
sum(if(excedido > 0 then excedido else 0)) quantidade

number of records with excedido > 0
sum(if(excedido > 0 then 1 else 0)) quantidade

Robert

> thanks!
>

THANKS!!!!!!!

Roger,

<< But, this sql don't result what I want, agregate field, dont count just
excedido > 0 but all excedido all registry, I need just where excedido > 0.
>>

Sorry, I missed that.  I see that Robert has corrected this for you,
however.

--
Tim Young
Elevate Software
www.elevatesoft.com