select ST,count(*) as 'Count' into #counta from ##temp group by st order by st
select ST,count(*) as 'HISPANIC' into #countb from ##temp  where spanish = 'y' group by st order by st

select a.ST,COUNT,hispanic from #counta a join #countb b on b.st = a.st
group by a.st,COUNT,hispanic

select st,COUNT(st),(case spanish when 'Y' then COUNT(st) end) from ##temp group by st order by st

quote:

---

Originally posted by albertkohl

got a question. i have a table of client information, and inside that we have a column flagging the client as "SPANISH" or not...

is there a way to run counts w/o having to make multipule passes, and at the same time not writing it into a temp table?

for example, this is what i'm doing now:

select ST,count(*) as 'Count' into #counta from ##temp group by st order by st
select ST,count(*) as 'HISPANIC' into #countb from ##temp  where spanish = 'y' group by st order by st

select a.ST,COUNT,hispanic from #counta a join #countb b on b.st = a.st
group by a.st,COUNT,hispanic

i tried something like this, but i know it doesnt work:

select st,COUNT(st),(case spanish when 'Y' then COUNT(st) end) from ##temp group by st order by st

---