CODEWITHSUNDEEP
sqlpostgresqlaggregate
user12587364
user12587364

Reputation:

SELECT column based on conditions

I have a table similar to the following:

+---------+--------------+
| user_id | distance     |
+---------+--------------+
|     101 | 12.05        |
|     103 | 4.8          |
|     207 | 37.1         |
|     991 | 3.51         |
|     215 | 15.9         |
+---------+--------------+

Then I want the number of users that cover different range of distances: 0-5km as short_distance, 5-10km as medium_distance, >10km as long_distance.

I'm kind of confused when aggregating.

Upvotes: 1

Views: 35

Answers (2)

Akhilesh Mishra
Akhilesh Mishra

Reputation: 6130

You want the count of users category wise. Try This

select 
       case 
         when distance > 10 then 'long_distance'
         when distance > 5  then 'medium_distance'
         else 'short_distance'
      end as "distance_type", count(*) as "Count"
from user_distance

group by "distance_type"

DEMO

Upvotes: 1

user330315
user330315

Reputation:

use a CASE expression:

select user_id, 
       case 
         when distance <= 5 then 'short distance'
         when distance <= 10 then 'medium distance'
         else 'long distance'
       end as what
from the_table;

Upvotes: 2

Related Questions