How to calculate the 2 and more than customers visited r?ooms-CodePudding

i having the table in which consists of customers numbers as below,

RoomId,Number of Customers
1,2
2,4
3,5

Here i would like to get categorize this data like below,

Category,RoomID
TwoCustomers,1
Morethan2Customers,2

i tried to count(number of customers)<2 then it created new column as two customers and if it is greater than 2 columns then append with more than 2 customers. which finally leads below results,

Category,RoomID
Morethan2Customers,3

Can anyone help me to solve this? Did i make mistake in count calculation or need to anyother approach in sql querying?

CodePudding user response：

You can use a case expression as the aggregation key:

select (case when num_customers = 2 then 'TwoCustomers'
             else 'Morethan2Customers'
        end) as which, count(*)
from t
where num_customers >= 2
group by (case when num_customers = 2 then 'TwoCustomers'
               else 'Morethan2Customers'
          end) ;