Question 90403 in PostgreSQL Question posted in
The official documentation can be found here.

Sql query – Order by number of true values in the columns – Postgresql

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
Tags:

3

Answers


  1. 0

    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
)
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
  3. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search