user1298426
user1298426

Reputation: 3717

Sql query - Order by number of true values in the columns

I have a table like this

enter image description here

I need to get the validity order by the number of true values in the last 4 columns. For e.g. the output of the below query should be

1110  // 4 true values
1001  // 2 true values
1000 // 1 true value

Upvotes: 0

Views: 65

Answers (3)

Mehmet Topçu
Mehmet Topçu

Reputation: 1364

You can use subquery;

SELECT subquery.VALIDITY,
concat(CAST ((subquery.TAXI + subquery.CAR + subquery.TRUCK + subquery.BIKE) AS TEXT) , ' True values') as COUNTTRUEVALUES
FROM (select VALIDITY,
CASE WHEN TAXI THEN 1 ELSE 0 END AS TAXI,
CASE WHEN CAR THEN 1 ELSE 0 END AS CAR,
CASE WHEN TRUCK THEN 1 ELSE 0 END AS TRUCK,
CASE WHEN BIKE THEN 1 ELSE 0 END AS BIKE
FROM YourTableName) as subquery
ORDER BY 2 DESC

output

VALIDITY COUNTTRUEVALUES

1110      4 True values
1001      2 True values
1000      1 True values

Upvotes: 0

forpas
forpas

Reputation: 164139

Convert the boolean values to integers and order descending by their total:

SELECT *
FROM tablename
ORDER BY taxi::int + car::int + truck::int + bike::int DESC;

See the demo.

Upvotes: 0

Leandro Bardelli
Leandro Bardelli

Reputation: 11588

What about something like this?

SELECT 
id,
validity,
taxi1 + car1 + truck1 + bike1
FROM 
(
SELECT
id,
validity
CASE WHEN taxi = 'TRUE' THEN 1 ELSE 0 END taxi1,
CASE WHEN car = 'TRUE' THEN 1 ELSE 0 END car1,
CASE WHEN truck = 'TRUE' THEN 1 ELSE 0 END truck1,
CASE WHEN bike = 'TRUE' THEN 1 ELSE 0 END bike1
FROM table
)

Upvotes: 0

Related Questions