CODEWITHSUNDEEP
sqlcountduplicatesteradata
user2820576
user2820576

Reputation: 125

teradata sql multiple occurrences as single value

I am fighting with a query where I need to retrieve a list of IDs and a value.

Then I need to cast a new value for those IDs that have more than one value.

For example:

ID          COLOR
1           BLUE
1           GREEN
2           ORANGE

What I want to return is like this:

ID          COLOR
1           MULTIPLE
2           ORANGE

So far, I've used ROW_NUMBER() and OVER(PARTITION BY) to retrieve all the results and number them.

But then I get stuck going in circles.

I can limit by those IDs that have multiple occurrences (where the row count >=2) and I get those IDs that need to be cast as "MULTIPLE".

BUT: Those same values are also included if I limit to IDs that have only one value (=1), because the multiple value IDs also have a row number 1. I wind up with an invalid result because I'm counting the multiple-value-IDs twice, kind of like this:

ID         COLOR
1          BLUE
1          MULTIPLE
2          ORANGE

So the question is: how can I get all the IDs with multiple values, separate from those that only have one value?

Upvotes: 1

Views: 3307

Answers (2)

Esteban Elverdin
Esteban Elverdin

Reputation: 3582

Try this:

SELECT id, 
CASE WHEN COUNT(color) > 1 THEN 'MULTIPLE'
     ELSE MAX(color)
END AS color
FROM Table1
GROUP BY id

I tested in SQLFIDDLE http://sqlfiddle.com/#!6/daf77/4 and seems to work fine

Upvotes: 4

Rob Paller
Rob Paller

Reputation: 7786

Here is one way to accomplish this using window aggregate functions:

CREATE VOLATILE TABLE MyTable, NO FALLBACK
(MyID SMALLINT NOT NULL,
 MyColor VARCHAR(10) NOT NULL
)
PRIMARY INDEX (MyID)
ON COMMIT PRESERVE ROWS;

INSERT INTO MyTable VALUES (1, 'BLUE');
INSERT INTO MyTable VALUES (1, 'GREEN');
INSERT INTO MyTable VALUES (2, 'ORANGE');

SELECT *
  FROM MyTable;

SELECT MyID
     , CASE WHEN MyCount = 1
            THEN MyColor
            ELSE 'MULTIPLE'
       END AS MyNewColor
  FROM (SELECT MyID
             , MyColor
             , ROW_NUMBER() OVER (PARTITION BY MyID 
                                  ORDER BY MyColor)   AS MyRowNum
             , COUNT(*) OVER (PARTITION BY MyID)      AS MyCount
          FROM MyTable
        ) DT1
  WHERE MyRowNum = 1;

Results:

MyID  MyNewColor
----  ----------
2     ORANGE
1     MULTIPLE

Upvotes: 1

Related Questions