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.