Some days I just can't think my way out of...

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)

Some days I just can't think my way out of...

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-21 : 14:20:29

CREATE TABLE MyTable (
UserID INT ,
Bonus INT
)

'Bonus' has values of 1, 2 or 3

I need a query that will count the number of UserIDs that have only one distinct bonus value, 2 distinct bonus values and 3 etc.

SELECT count(distinct bonus)

FROM MyTable

Group By UserID

Having count(distinct bonus) = 1

Gives a recordset of rows with UserIDs with only 1 distinct bonus. But this seems painful to go from here to count the rows

select count(*)

from (

SELECT count(distinct bonus)

FROM MyTable

Group By UserID

Having count(distinct bonus) = 1

)

Is there a better way to derive the count of users with 1 distinct bonus?

Sam

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-21 : 16:02:49

Don't think this is easier

select count(distinct UserID)
from MyTable t1
where not exists (select * from MyTable t2 where t1.UserID = t2.UserID and t1.bonus <> t2.bonus)

Your query would look netter with
select UserID

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

r937
Posting Yak Master

112 Posts

Posted - 2003-04-21 : 17:43:33

select sub.bonuses, count(*) as users
  from (
       select count(distinct bonus) as bonuses
        from MyTable
       group by userid
       ) as sub
group by sub.bonuses

not tested

rudy
http://r937.com/

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-21 : 19:02:32

Thanks Rudy,

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources