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.

count()

2 Answers
2

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

count ( distinct <column> )

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


Use aggregation with distinct for room

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


Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.