Counting the rows of a column where the value of a different column is 1

I am using a select count distinct to count the number of records in a column. However, I only want to count the records where the value of a different column is 1.

So my table looks a bit like this:

Name------Type

abc---------1

def----------2

ghi----------2

jkl-----------1

mno--------1

and I want the query only to count abc, jkl and mno and开发者_JAVA技巧 thus return '3'.

I wasn't able to do this with the CASE function, because this only seems to work with conditions in the same column.

EDIT: Sorry, I should have added, I want to make a query that counts both types. So the result should look more like: 1---3 2---2

SELECT COUNT(*) 
  FROM dbo.[table name] 
  WHERE [type] = 1;

If you want to return the counts by type:

SELECT [type], COUNT(*)
  FROM dbo.[table name]
  GROUP BY [type]
  ORDER BY [type];

You should avoid using keywords like type as column names - you can avoid a lot of square brackets if you use a more specific, non-reserved word.

I think you'll want (assuming that you wouldn't want to count ('abc',1) twice if it is in your table twice):

select count(distinct name) 
  from mytable
 where type = 1

EDIT: for getting all types

select type, count(distinct name)
  from mytable
 group by type
 order by type

select count(1) from tbl where type = 1

;WITH MyTable (Name, [Type]) AS
(
SELECT 'abc', 1
UNION
SELECT 'def', 2
UNION
SELECT 'ghi', 2
UNION
SELECT 'jkl', 1
UNION
SELECT 'mno', 1
) 
SELECT COUNT( DISTINCT Name)
FROM MyTable
WHERE [Type] = 1