CODEWITHSUNDEEP
sqlsql-servert-sqlaggregate
Dayton Brown
Dayton Brown

Reputation: 1240

T-SQL query and group by reporting help

So I have some data that looks like this.

`USERID1 USERID2`
    1        10
    2        20
    2        30
    3        40
    3        50
    1        10
    2        20
    2        30
    3        50

I want a query that produces the following

`USERID1     COUNT`
    2        2
    3        2

It's a group by query that shows me the count of unique USERID2 for each USERID1 that has more than 1 USERID2 associated with it. God I hope you aren't as confused as I am by that last statement.

Upvotes: 1

Views: 246

Answers (5)

Andy Shellam
Andy Shellam

Reputation: 15535

Does this work?

SELECT USERID1, COUNT(DISTINCT USERID2) 
  FROM [table] 
 GROUP BY USERID1 
HAVING COUNT(DISTINCT USERID2) > 1

Upvotes: 2

Jimmy
Jimmy

Reputation: 9815

select userid1, count(userID1) as [count] from tablename group by userid1 order by userid1

Upvotes: 0

exiter2000
exiter2000

Reputation: 548

How about this??

select userid1, count(distinct userid2) from nameOfTable where userid1 > 1 group by userid1

Upvotes: 0

MJB
MJB

Reputation: 7686

How about:

select userid1, count(*) from tablename group by userid1 having count(*) > 1

Upvotes: 2

Ashish Gupta
Ashish Gupta

Reputation: 15139

Could you try something like this:-

SELECT UserID1, COUNT(UserID2) FROM Table1 GROUP BY UserId1
HAVING COUNT(UserID2)>1

Upvotes: 1

Related Questions