asked on

selectively counting a char field

I am creating a sql and executing it in SQL Server 2000. I'm trying to count a field where it equals a certain value. In this case, I am trying to count the field POC where it equals 'Y', and putting that into another table. The SQL below worked until I tried the "count(case when...." case.

Is this possible?

------------------------------
update t1 set t1.AvgUnitSz = t2.fld1, t1.TotHHs = t2.fld2, t1.AvgHHSz = t2.fld3 from tblBaseBG t1
inner join (select bg_id, avg(UnitSize) fld1, count(case when POC='Y' then 1 else 0) fld2, avg(Hhcount) fld3 from tblLL_HH_AL
group by bg_id) t2 on t1.NAME = t2.bg_id;

Crag

Without testing it what about changing the count to a sum?

Aneesh

seems like ok, you need to do some testing

Doug Foster

ASKER

That seems logical.... but.....

it gives me: Line 2: Incorrect syntax near ')'.

I'm testing it in query analyzer, but will run it from Excel VBA ADODB. I'm not sure if that will make a difference. I simplified the query only to include the one field....

update t1 set t1.TotHHs = t2.fld2 from tblBaseBG t1
inner join (select bg_id, sum(case when POC='Y' then 1 else 0) fld2 from tblLL_HH_AL
group by bg_id) t2 on t1.NAME = t2.bg_id;

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Doug Foster

ASKER

Thanks aneeshattingal.

I didn't have the END in the case statement. That did the trick.