CODEWITHSUNDEEP
sqloracle11gaggregate-functions
WW.
WW.

Reputation: 24311

SQL aggregate function to return single value if there is only one, otherwise null

I'm looking for the best way to achieve an aggregate function that does this:-

Here's some sample data:

CREATE TABLE EXAMPLE
( ID  NUMBER(3),
  VAL VARCHAR2(3));

INSERT INTO EXAMPLE VALUES (1,'A');
INSERT INTO EXAMPLE VALUES (2,'A');
INSERT INTO EXAMPLE VALUES (2,'B');
INSERT INTO EXAMPLE VALUES (3,null);
INSERT INTO EXAMPLE VALUES (3,'A');
INSERT INTO EXAMPLE VALUES (4,'A');
INSERT INTO EXAMPLE VALUES (4,'A');

The SQL should be something like:-

SELECT ID, ????( VAL ) ONLY_VAL
FROM EXAMPLE 
GROUP BY ID
ORDER BY ID

The result I am after should look like this:-

 ID       ONLY_VAL
  1       A
  2       
  3       
  4       A

In the real thing, I want to do this on multiple VAL columns (grouped by the same ID). There would be several hundred records per ID.

I thought this was an interesting problem The only solution I have is a mess of NVL, MIN and MAX and it seems like there should be a neater way.

Upvotes: 4

Views: 1147

Answers (1)

John Woo
John Woo

Reputation: 263933

Will this work for for your original data?

SELECT  ID, 
        CASE WHEN COUNT(DISTINCT VAL) = 1 AND COUNT(ID) = COUNT(VAL)
             THEN MAX(VAL)
             ELSE NULL
        END ONLY_VAL
FROM    EXAMPLE
GROUP   BY ID
ORDER   BY ID

Upvotes: 2

Related Questions