CODEWITHSUNDEEP
sqlsql-server
Pr0no
Pr0no

Reputation: 4099

How to group by two columns?

Please consider below. I wish to list the number of times that each users made reservations for a conference room, and how many different rooms he/she used:

room  user
----------
1     1    
2     1
3     2
4     3
4     3

Desired output:

user  reservations   rooms
-------------------------
1     2              2
2     1              1
3     2              1

Can anyone tell me how to do this?

I now have:

select [user], count(1)
from   [table]
group by [user]

But this doesn't give me the rooms count. Adding another count() doesn't work.

Upvotes: 0

Views: 41

Answers (2)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

Use aggregation with distinct for room

  Select  user,  count(*) reservation_ numbr,count(distinct room) as rooms
  from t group by user

Upvotes: 0

Squirrel
Squirrel

Reputation: 24763

you need a count ( distinct <column> ) for the room

select [user], count(*) as reservations, count(distinct room) as rooms
from   [table]
group by [user]

Upvotes: 3

Related Questions