CODEWITHSUNDEEP
sqloracle-database
GummiBoat
GummiBoat

Reputation: 15

SQL Grouping entries with a different value

Let's assume I have a report that displays an ID and VALUE from different tables

  | ID | VALUE |
  |----|-------|
1 | 1  | 1     |
2 | 1  | 0     |
3 | 1  | 1     |
4 | 2  | 0     |
5 | 2  | 0     |

My goal is to display this table with grouped IDs and VALUEs. My rule to grouping VALUEs would be "If VALUE contains atleast one '1' then display '1' otherwise display '0'".

My current SQL is (simplified)

SELECT
    TABLE_A.ID,
    CASE
        WHEN TABLE_B.VALUE = 1 OR TABLE_C.VALUE NOT IN (0,1,2,3)
        THEN 1
        ELSE 0
    END AS VALUE
FROM TABLE_A, TABLE_B, TABLE_C
GROUP BY
    TABLE_A.ID
    (CASE
        WHEN TABLE_B.VALUE = 1 OR TABLE_C.VALUE NOT IN (0,1,2,3)
        THEN 1
        ELSE 0
    END)

The output is following

  | ID | VALUE |
  |----|-------|
1 | 1  | 1     |
2 | 1  | 0     |
3 | 2  | 0     |

Which is half way to the output I want

  | ID | VALUE |
  |----|-------|
1 | 1  | 1     |
2 | 2  | 0     |

So my Question is: How do I extend my current SQL (or change it completely) to get my desired output?

Upvotes: 1

Views: 63

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Assuming value is always 0 or 1, you can do:

select id, max(value) as value
from t
group by id;

If value can take on other values:

select id,
       max(case when value = 1 then 1 else 0 end) as value
from t
group by id;

Upvotes: 0

Nikhil Shetkar
Nikhil Shetkar

Reputation: 346

If you are having only 0 and 1 as distinct values in FOREIGN_VALUE column then using max() function as mentioned by HoneyBadger in the comment will fulfill your requirement.

SELECT
   ID,
   MAX(FOREIGN_VALUE) AS VALUE
FROM (SELECT
         ID,
        CASE WHEN FOREIGN_VALUE = 1
             THEN 1
             ELSE 0
        END AS FOREIGN_VALUE
      FROM TABLE, 
      FOREIGN_TABLE)
GROUP BY
ID;

Upvotes: 1

Related Questions