SQLTeam.com | Weblogs | Forums

SQL Case to sum total counts by names


I have sql query that returns total counts by name.

Select distinct name_title, count(name_title)
from table_name

These returns:
Name_title Count
a 3
b 10
c 5
d 4

I need to further work the data by grouping them by Name_Title column:

when Name_title in('a','b') then sum(count(name_title)) end as dept1
when Name_title in('c','d') then sum(count(name_title)) end as dept2

the desired output:
name_title count
dept1 13
dept2 9

any assistant or direction greatly appreciated.

create table #depts (
    name_title varchar(50) not null,
    dept varchar(50) not null
insert into #depts select 'a', 'dept1'
insert into #depts select 'b', 'dept1'
insert into #depts select 'c', 'dept2'
insert into #depts select 'd', 'dept2'

    ISNULL(d.dept, 'Other') AS dept,
    COUNT(*) AS count
from table_name tn
left outer join #depts d on d.name_title = tn.name_title
order by dept


Scott's post is spot on but it would probably be better if you used his good example to make a permanent table for "Dept".