I would do the following :



SELECT m.Id, tr.Text,
sum(m.Valor) as Valor,
sum(m.Valor / oi.Area) as ValorM2,
sum(m.Valor / oi.Saldo) * 100 as PerTotal
FROM table1 M
LEFT JOIN table2 TR ON (tr.ID = m.ID)
LEFT JOIN table3 OI ON (oi.ID = m.ID)
WHERE (M.ID=1) and oi.Area<>0 and oi.Saldo<>0
GROUP BY m.id, tr.text
ORDER BY m.id
union all
SELECT m.Id, tr.Text,
sum(m.Valor) as Valor,
null as ValorM2,
sum(m.Valor / oi.Saldo) * 100 as PerTotal
FROM table1 M
LEFT JOIN table2 TR ON (tr.ID = m.ID)
LEFT JOIN table3 OI ON (oi.ID = m.ID)
WHERE (M.ID=1) and oi.Area=0 and oi.Saldo<>0
GROUP BY m.id, tr.text
ORDER BY m.id
union all

SELECT m.Id, tr.Text,
sum(m.Valor) as Valor,
sum(m.Valor / oi.Area) as ValorM2,
null as PerTotal
FROM table1 M
LEFT JOIN table2 TR ON (tr.ID = m.ID)
LEFT JOIN table3 OI ON (oi.ID = m.ID)
WHERE (M.ID=1) and oi.Area<>0 and oi.Saldo=0
GROUP BY m.id, tr.text
ORDER BY m.id


Tiberiu

----- Original Message -----
From: Luis Madaleno
To: firebird-support@yahoogroups.com
Sent: Tuesday, May 03, 2005 4:36 PM
Subject: Re: [firebird-support] select problem


Hi,

What I want to do here is just trying to avoid a division by zero error
when oi.area is zero.
my original query works fine:

SELECT m.Id, tr.Text,
sum(m.Valor) as Valor,
sum(m.Valor / oi.Area) as ValorM2,
sum(m.Valor / oi.Saldo) * 100 as PerTotal
FROM table1 M
LEFT JOIN table2 TR ON (tr.ID = m.ID)
LEFT JOIN table3 OI ON (oi.ID = m.ID)
WHERE (M.ID=1)
GROUP BY m.id, tr.text
ORDER BY m.id

but when oi.area or oi.saldo is zero the query gives an error.
since there is no IIF() function, the only solution is to use a CASE
but I guess I wont be able to do this.

It is a good thing for FB2 to have or an IIF() function or this kind of
CASE use possible.

Regards and thanks,

Luis



Ann W. Harrison wrote:

>Hans wrote:
>
>
>>Usually you have to add the fields in the SUM( ) calculations to the GROUP
>>BY
>>statement.
>>
>>
>
>No, SUM is an aggregate and that field doesn't have to be in the GROUP
>BY. The problem field is here:
>
> > CASE oi.area
>
>oi.area is not in the GROUP BY and CASE is not an aggregate.
>
>
>Regards,
>
>
>Ann
>
>
>
>Visit http://firebird.sourceforge.net and click the Resources item
>on the main (top) menu. Try Knowledgebase and FAQ links !
>
>Also search the knowledgebases at http://www.ibphoenix.com
>Yahoo! Groups Links
>
>
>
>
>
>
>
>
>
>
>


Visit http://firebird.sourceforge.net and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com



---------------------------------
Yahoo! Groups Links

To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



---------------------------------
Do you Yahoo!?
Yahoo! Mail - Easier than ever with enhanced search. Learn more.

[Non-text portions of this message have been removed]