multiple results query

Hi, I'm new to SQL, first stared using some rudiments of SQL in Access, now I moved to biger flights

Ok this is my question:

I've a table "XPTO" with two colums

Name Type

aaaa 01

bbbb 05

cccc 01

dddd 05

eeee 01

rrrrrrr 01

What I need is for the query to return me following:

UP Down

4 2

I using somethig like this:

SELECT count(*) AS "UP", count(*) AS "Down" FROM "XPTO"
WHERE ("XPTO"."Type" = '01') AND ("XPTO"."Type"= '05')

Wich returns me the following

UP Down

0 0

Because there is no value that complies with the two conditions.

I need the red part of the code to refer exclusively to the alias Down

Thanks in advanced for any help.

Perhaps something like:

Code Snippet

SET NOCOUNT ON

DECLARE @.XPTO table
( [Name] varchar(10),
[Type] char(2)
)

INSERT INTO @.XPTO VALUES ( 'aaaa', '01' )
INSERT INTO @.XPTO VALUES ( 'bbbb', '05' )
INSERT INTO @.XPTO VALUES ( 'cccc', '01' )
INSERT INTO @.XPTO VALUES ( 'dddd', '05' )
INSERT INTO @.XPTO VALUES ( 'eeee', '01' )
INSERT INTO @.XPTO VALUES ( 'rrrrrrr', '01' )

SELECT
Up = sum( CASE Type WHEN '01' THEN 1 END ),
Down = sum( CASE Type WHEN '05' THEN 1 END )
FROM @.XPTO

Up Down
-- --
4 2

|||

You can do below:

Code Snippet

SELECT sum(case t."Type" when '01' then 1 end) AS "UP"

, sum(case t."Type" when '05' then 1 end) AS "Down"

FROM "XPTO" AS t

-- This filter helps restrict the rows and provides better performance if you have an

-- index on the type column.

WHERE t."Type" in ( '01', '05')

|||As Umachandar indicated, the WHERE clause filter is a good idea IF there are more values than '01' and '05' in the [Type] column -otherwise it seems to me that it will not have any significant effect if the [Type] is not indexed -all rows will have to be read.