We help IT Professionals succeed at work.

selectively counting a char field

Doug Foster
Doug Foster asked
on
314 Views
Last Modified: 2007-10-10
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;
Comment
Watch Question

Commented:
Without testing it what about changing the count to a sum?
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
seems like ok, you need to do some testing

Author

Commented:
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;
Database Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks aneeshattingal.

I didn't have the END in the case statement.  That did the trick.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.