quote:

---

Originally posted by micrapip

Hi I haven't done SQL in about 4-5 years so I'm a bit rusty...

Have the following table...

transit type
00018 Laptop
00018 Laptop
00018 Desktop
00025 Laptop
00025 Desktop

I can do a select destinct that will show the following

transit
00018
00025

What I'm looking for is select distinct transit and count the type = 'Laptop' so the output looks like this...

Transit Count
00018 2
00025 1

Can someone help me out?

this is all from one table so no need for joins etc

thanks

Micrapip

---

SELECT transit,SUM(CASE WHEN type='Laptop' THEN 1 ELSE 0 END) AS Count
FROM Table
GROUP BY transit

SELECT transit,COUNT(CASE WHEN type='Laptop' THEN type ELSE NULL END) AS Count
FROM Table
GROUP BY transit

create table #test(
transit char(5),
[type] varchar(25)
)
insert #test
select '00018', 'Laptop' union all
select '00018', 'Laptop' union all
select '00018', 'Desktop' union all
select '00025', 'Laptop' union all
select '00025', 'Desktop'

select  
transit,
[type],
count(*) as counter
from #test
group by transit,[type]
order by transit,[type]

-- gives you:
-- 00018	Desktop	1
-- 00018	Laptop	2
-- 00025	Desktop	1
-- 00025	Laptop	1
drop table #test

select transit, count(*) as [count] from #test where type = 'laptop' group by transit


transit count
------- -----------
00018   2
00025   1