Two things,
1. As you query stands, nothing will be summed. the sum expression is inly applicable in sql when the data is grouped. Looking at the query, you should be grouping by group_.gcode. To do this add the clause GROUP BY group_.gcode on the end of your sql statement.
2. If you need to sum fields for different conditions, I would use an IIF()
eg: SELECT SUM(
), , 0) AS , SUM( ), , 0) AS FROM...
Mace
>Hi Mace
>
>Thanks for the reply.
>But if I use the where clause how do i specify that i want to sum one field for a certain condition and another field for for a different condition?
>
>Do u have a solution?
>
>Fida
>
>>>Hi All
>>>
>>>Can anyone please help with this statement :
>>>
>>>I get an error say unrecognised phrase/keyword .
>>>
>>>select group_.gcode,group_.desc_,sum(assets.price) for (empty( assets.trans_sold) .or. assets.trans_sold = "I" .or. isnull(assets.trans_sold)), ;
>>>sum(assets.depprevyrs) for assets.trans_sold = "S" ;
>>>from group,assets ;
>>>where assets.group_ = group_.gcode
>>>
>>>Thanks in advance
>>>Fida
>>
>>
>>Fida,
>>
>>You can't use the for clause in a select statement. This should be part of the where clause.
>>
>>Mace