Return only one row for this example

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Return only one row for this example

Author

Topic

rav3n0u5
Yak Posting Veteran

88 Posts

Posted - 2006-02-01 : 00:21:49

I think I am just missing something... Right now, my code returns 2 rows with a NULL cell and the data I want in one row, and the other data in another row with NULL. I just want it to appear on one line.

Below is some stuff to play with:


CREATE TABLE people
(
  peopleID tinyint identity
  , personName varchar (20)
  , favColorID tinyint
  , favPetID tinyint
)

INSERT INTO people (personName, favColorID, favPetID)
VALUES ('hello', 1, 1)

INSERT INTO people (personName, favColorID, favPetID)
VALUES ('hell', 1, 2)

INSERT INTO people (personName, favColorID, favPetID)
VALUES ('helo', 2, 1)

INSERT INTO people (personName, favColorID, favPetID)
VALUES ('heo', 2, 2)

INSERT INTO people (personName, favColorID, favPetID)
VALUES ('ho', 2, 1)

INSERT INTO people (personName, favColorID, favPetID)
VALUES ('lo', 1, 2)

INSERT INTO people (personName, favColorID, favPetID)
VALUES ('hllo', 1, 1)

INSERT INTO people (personName, favColorID, favPetID)
VALUES ('elo', 1, 2)

INSERT INTO people (personName, favColorID, favPetID)
VALUES ('hel', 1, 2)

This is my query:


SELECT
  Color1 = (SELECT COUNT (p.favColorID) WHERE favColorID = 2)
  , Color2 = (SELECT COUNT (p.favColorID) WHERE favColorID = 1)

FROM people p

GROUP BY p.favColorID

nathans
Aged Yak Warrior

938 Posts

Posted - 2006-02-01 : 01:36:13

Try:

select sum(case when favColorID = 1 then 1 else 0 end) Color1
...

Nathan Skerl

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-01 : 01:39:03

[code]select (select count(*) as Color2 from people where favColorID = 2) as Color1,
(select count(*) as Color2 from people where favColorID = 1) as Color2

or

select sum(case when favColorID = 2 then 1 else 0 end) as Color1,
sum(case when favColorID = 1 then 1 else 0 end) as Color2
from people[/code]

----------------------------------
'KH'

rav3n0u5
Yak Posting Veteran

88 Posts

Posted - 2006-02-01 : 01:42:17

Coolio. Thanks a bunch!

rav3n0u5
Yak Posting Veteran

88 Posts

Posted - 2006-02-01 : 01:58:01

Hmm... now you got me thinking...

which method would be faster if there were about 1000 colors, and several conditions added?

select count(*) or sum(case...) ???

Without doing much inspection, it seems the former is faster.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-01 : 02:04:28

run both query in Query Analyser and see the execution plan

----------------------------------
'KH'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources