Hi,

I found the solution to this. In case anyone needs it:

SELECT m.Id, tr.Text,
SUM(m.Valor) as Valor,
SUM(CASE WHEN oi.area=0 THEN 0 ELSE (m.Valor / oi.Area) END) 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

The CASE need to be inside the SUM(..)

Thanks to all.

Luis


Luis Madaleno wrote:

>Hi,
>
>Can anyone help me with this select statement?
>
>SELECT m.Id, tr.Text,
>sum(m.Valor) as Valor,
>CASE oi.area
>WHEN 0 THEN m.Valor
>ELSE (m.Valor / oi.Area)
>END 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
>
>after adding the CASE statement this query returns error -104.
>
>Any help?
>
>Regards,
>
>Luis
>
>
>
>
>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
>
>
>
>
>
>
>
>
>
>
>