CODEWITHSUNDEEP
sqlpostgresqlselect
user2352131
user2352131

Reputation: 29

Postgres query to get value if all in column are equal

I have a table with metadata and I need to get the value if they are all equal, or if they are not equal then return 'mixed', or if all are null then return null. 

id   |   color   |   size  |   shape   |  area
 1   |   blue    |   small |   square  | 
 2   |           |   small |   circle  |
 3   |   blue    |   small |   oval    |
 4   |   blue    |   small |   oval    |

select distinct color, size, shape, area
from table
where id = 1 or id = 2 or id = 3;

The expected result would be one result with the following-

'mixed', 'small', 'mixed', null

Upvotes: 0

Views: 3020

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

select id,
       (case when min(color) is not distinct from max(color) and
                  min(size) is not distinct from max(size) and
                  min(shape) is not distinct from max(shape) and
                  min(area) is not distinct from max(area) 
             then 'same'
             else 'mixed'
        end)
from table
where id in (1, 2, 3)
group by id;

You can also do this per column as well.

EDIT:

I think the question has changed a bit since I first answered (or at least I misunderstood it):

select (case when min(color) = max(color) and count(*) = count(color) or
                  min(color) is null
             then min(color) else 'mixed'
        end) as color,
       (case when min(size) = max(size) and count(*) = count(size) or
                  min(size) is null
             then min(size) else 'mixed'
        end) as size,
       (case when min(color) = max(shape) and count(*) = count(shape) or
                  min(shape) is null
             then min(shape) else 'mixed'
        end) as shape,
       (case when min(area) = max(area) and count(*) = count(area) or
                  min(area) is null
             then min(area) else 'mixed'
        end) as area
from table
where id in (1, 2, 3);

Upvotes: 1

MiloradM
MiloradM

Reputation: 36

I will try to use CTE's

with
   -- distinct values
   scolor as (select distinct color from mytable),
   ssize as  (select distinct size  from mytable),
   sshape as (select distinct shape from mytable),
   sarea as  (select distinct area  from mytable)
select
   case
      when (select count(*) from scolor) > 1
         then 'mixed'
      when (select count(*) from scolor) = 1 and (select * from scolor) is null
         then null
      else
         (select * from scolor)
   end,
   case
      when (select count(*) from ssize) > 1
         then 'mixed'
      when (select count(*) from ssize) = 1 and (select * from ssize) is null
         then null
      else
         (select * from ssize)
   end,
   case
      when (select count(*) from sshape) > 1
         then 'mixed'
      when (select count(*) from sshape) = 1 and (select * from sshape) is null
         then null
      else
         (select * from sshape)
   end,
   case
      when (select count(*) from sarea) > 1
         then 'mixed'
      when (select count(*) from sarea) = 1 and (select * from sarea) is null
         then null
      else
         (select * from sarea)
   end
from mytable
limit 1;

This solution works well with your test data. Just check. (I don't want to use "table" as table name, so I use "mytable")

Upvotes: 0

carrot
carrot

Reputation: 96

Try..

    SELECT 
CASE WHEN count(DISTINCT color)=0 THEN 'Null' WHEN count(DISTINCT color)+sum(case when color is null then 1 else 0 end) =1 THEN MAX(color) ELSE 'Mixed' END AS color,

CASE WHEN count(DISTINCT size)=0 THEN 'Null' WHEN count(DISTINCT size)+sum(case when size is null then 1 else 0 end) =1 THEN MAX(size) ELSE 'Mixed' END AS size,

CASE WHEN count(DISTINCT shape)=0 THEN 'Null' WHEN count(DISTINCT shape)+sum(case when shape is null then 1 else 0 end) =1 THEN MAX(shape) ELSE 'Mixed' END AS shape,

CASE WHEN count(DISTINCT area)=0 THEN 'Null' WHEN count(DISTINCT area)+sum(case when area is null then 1 else 0 end) =1 THEN MAX(area) ELSE 'Mixed' END AS area

            FROM   my_table
WHERE  id IN (1, 2, 3)

Upvotes: 0

Dan Getz
Dan Getz

Reputation: 18227

Try:

SELECT 
  CASE count(distinct color) WHEN 0 THEN NULL WHEN 1 THEN 
    CASE count(color)=count(*) WHEN true THEN min(color) ELSE 'mixed' END ELSE 'mixed' END color,
  CASE count(distinct size) WHEN 0 THEN NULL WHEN 1 THEN 
    CASE count(size)=count(*) WHEN true THEN min(size) ELSE 'mixed' END ELSE 'mixed' END size,
  CASE count(distinct shape) WHEN 0 THEN NULL WHEN 1 THEN 
    CASE count(shape)=count(*) WHEN true THEN min(shape) ELSE 'mixed' END ELSE 'mixed' END shape,
  CASE count(distinct area) WHEN 0 THEN NULL WHEN 1 THEN 
    CASE count(area)=count(*) WHEN true THEN min(area) ELSE 'mixed' END ELSE 'mixed' END area
FROM table ;

Upvotes: 1

Mureinik
Mureinik

Reputation: 312259

A neat trick is to select the max and min of the column and compare them:

SELECT CASE WHEN MAX(color) IS NOT DISTINCT FROM MIN(color)
            THEN MAX(color) ELSE 'mixed' END,
       CASE WHEN MAX(size)  IS NOT DISTINCT FROM MIN(size)
            THEN MAX(size)  ELSE 'mixed' END,
       CASE WHEN MAX(shape) IS  NOT DISTINCT FROM MIN(shape)
            THEN MAX(shape) ELSE 'mixed' END,
       CASE WHEN MAX(area)  IS  NOT DISTINCT FROM MIN(area)
            THEN MAX(area)  ELSE 'mixed' END
WHERE  id IN (1, 2, 3)
FROM   my_table

Upvotes: 1

Related Questions