Re: newbie-SQL Count

On Mar 26, 5:53 pm, s..._at_uaex.edu wrote:
> I have a table, there are more columns than this.
> I used this to keep it simple.
>
> ColA ColB Quantity
> 30 24 2
> 30 0 1
>
> I want to do
>
> UPDATE myTable SET Quantity= (SUM(Decode(ColA,NULL,0,0,0,1)
> +Decode(ColB,NULL,0,0,0,1) )
>
> I get an error message of :
> group function is not allowed here
>
> How can I count each Column that has a POSITIVE number and put the sum
> in QUANTITY?
>
> TIA
> Steve

Does this query have a group by clause?

Are you trying to just count the positive values on each row into the quantity column of the same row or sum them for the entire table?

I think posting the full where clause would be helpful to understanding your problem.

The sign function returns -1 for less than zero, 0 for 0, and 1 for > 0 and in the past was often used in a decode to allow identifying relative value of the variables to each other. Howerver the CASE statement generally makes this unnecessary.

select case when col1 <= 0 then 0 else 1 end ...

See SQL manual.

HTH -- Mark D Powell -- Received on Mon Mar 26 2007 - 18:41:26 CDT