-2

I have a table:

+-----------+-------+
|   risk    | count |
+-----------+-------+
| high      |     5 |
| low       |    30 |
| medium    |    81 |
| very high |     2 |
| very low  |    72 |
+-----------+-------+

I want to group certain values (low, very low) together into a new row named "Other". How should I do this ?

I know I need to create a new table based on select statement and I looked into group by but that only groups like names together. Is this even possible?

I want the result table to be like this:

+-----------+-------+
|   risk    | count |
+-----------+-------+
| high      |     5 |
| medium    |    81 |
| very high |     2 |
| Other     |   102 |
+-----------+-------+

closed as off-topic by Md Haidar Ali Khan, Colin 't Hart, mustaccio, Max Vernon, hot2use Sep 13 '18 at 12:47

If this question can be reworded to fit the rules in the help center, please edit the question.

2

I think the following query should produce the required result:

SELECT 
    DECODE(RISK,'very low','other','low','other',RISK) RISK,
    SUM(COUNT) COUNT 
FROM TEST_RESULT 
GROUP BY 
    DECODE(RISK,'very low','other','low','other',RISK);

You have not mentioned a table name, I have used TEST_RESULT.

Following is my complete worksheet:

CREATE TABLE TEST_RESULT (RISK VARCHAR2(20),COUNT NUMBER(5));

INSERT INTO TEST_RESULT VALUES ('high',5);
INSERT INTO TEST_RESULT VALUES ('low',30);
INSERT INTO TEST_RESULT VALUES ('medium',81);
INSERT INTO TEST_RESULT VALUES ('very high',2);
INSERT INTO TEST_RESULT VALUES ('very low',72);


SELECT 
    DECODE(RISK,'very low','other','low','other',RISK) RISK,
    SUM(COUNT) COUNT 
FROM TEST_RESULT 
GROUP BY 
    DECODE(RISK,'very low','other','low','other',RISK);

The question does not mention a database product, but it seemed to me like a standard SQL problem. I had Oracle DB available, so I used Oracle.

  • 1
    If your variant of SQL doesn't have the DECODE function, you can use CASE: CASE risk WHEN 'low' THEN 'Other' WHEN 'Very low' THEN 'Other' ELSE risk END should be equivalent to the DECODE statement, and more generically available. – RDFozz Sep 11 '18 at 20:22

Not the answer you're looking for? Browse other questions tagged or ask your own question.