[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 300
  • Last Modified:

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;
0
dougfosterNYC
Asked:
dougfosterNYC
  • 2
  • 2
1 Solution
 
CragCommented:
Without testing it what about changing the count to a sum?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
seems like ok, you need to do some testing
0
 
dougfosterNYCAuthor 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;
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
update t1 set t1.TotHHs = t2.fld2 from tblBaseBG t1
inner join (select bg_id, sum(case when POC='Y' then 1 else 0 END ) fld2 from tblLL_HH_AL
group by bg_id) t2 on t1.NAME = t2.bg_id;
0
 
dougfosterNYCAuthor Commented:
Thanks aneeshattingal.

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

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now