SELECT COUNT? - SQL Server Forums

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

SQL Server Development (2000)

SELECT COUNT?

Author

Topic

ywb
Yak Posting Veteran

55 Posts

Posted - 2006-02-10 : 18:04:22

Hi,

I have 2 tables ("User" & "UserColour") that link users to their favourite colours. A user may have more than one favourite colour, or he may also have no colour preference by having the NoColourPref bit equals to 1.

Here are some sample data:

User Table
==========

UserID   Username   NoColourPref
1            Peter         0
2            Paul         1
3            Mary         0

UserColour Table
===============

UserID   ColourID
1            2
1            3
3            2

Now I'd like to write a query that returns the count (just a number) of people that either like ColourID = 3 or with no colour preference.

How do I do that?

ywb.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-10 : 18:43:01

Here is one way... (this isn't homework is it?)


select	count(userid) as [count]
from	(
	select distinct userid from usercolour where colourid = 3
	union
	select userid from [user] where nocolourpref = 1
	) a

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources