Usually you have to add the fields in the SUM( ) calculations to the GROUP
BY
statement.

----- Original Message -----
From: "Luis Madaleno" <luis@...>
To: <firebird-support@yahoogroups.com>
Sent: Monday, May 02, 2005 12:54 PM
Subject: Re: [firebird-support] select problem


> The error description is:
> Invalid expression in the select list (not contained in either an
> aggregate function or the GROUP BY clause).
>
> SELECT m.Id, tr.Text,
> sum(m.Valor) as Valor,
> CASE oi.area
> WHEN 0 THEN sum(m.Valor)
> ELSE sum(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
>
>
>
> 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
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>
>
> 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
>
>
>
>
>
>
>