CODEWITHSUNDEEP
sqloracle-database
sqlpractice
sqlpractice

Reputation: 141

Return rows in oracle containing non-zero columns only

I have a table where one record can have multiple rows. Two columns of this row contains zero as well as non-zero values. I want to return all rows of this record only if both or atleast one column of this row have non-zero values. How to achieve this ?

Item || Col1 ||Col2|| Attr
Item1 || 1 || 0 || Attr1
Item1 || 1 || 1 || Attr2
Item1 || 0 || 0 || Attr3
Item2 || 1 || 0 || Attr2
Item2 || 0 || 1 || Attr1
Item3 || 1 || 1 || Attr4
Item4 || 0 || 1 || Attr4
Item4 || 1 || 1 || Attr4

The result I want -
Item || Attr
Item2 || Attr2
Item2 || Attr1
Item3 || Attr4
Item4 || Attr4

Third row of Item1 has zero in both the columns so Item1 should be discarded. What is the best way to achieve this through PL/SQL ?

Upvotes: 0

Views: 53

Answers (3)

MT0
MT0

Reputation: 168281

You can do it in a single table-scan (without any self-join or correlated sub-queries) using analytic functions and conditional aggregation:

SELECT DISTINCT
       Item,
       Attr
FROM   (
  SELECT Item,
         Attr,
         COUNT(CASE WHEN col1 = 0 AND col2 = 0 THEN 1 END)
           OVER (PARTITION BY Item) AS num_zero
  FROM   table_name
)
WHERE  num_zero = 0;

Which, for the sample data:

CREATE TABLE table_name (Item, Col1, Col2, Attr) AS
  SELECT 'Item1', 1, 0, 'Attr1' FROM DUAL UNION ALL
  SELECT 'Item1', 1, 1, 'Attr2' FROM DUAL UNION ALL
  SELECT 'Item1', 0, 0, 'Attr3' FROM DUAL UNION ALL
  SELECT 'Item2', 1, 0, 'Attr2' FROM DUAL UNION ALL
  SELECT 'Item2', 0, 1, 'Attr1' FROM DUAL UNION ALL
  SELECT 'Item3', 1, 1, 'Attr4' FROM DUAL UNION ALL
  SELECT 'Item4', 0, 1, 'Attr4' FROM DUAL UNION ALL
  SELECT 'Item4', 1, 1, 'Attr4' FROM DUAL;

Outputs:

ITEM ATTR
Item2 Attr2
Item2 Attr1
Item3 Attr4
Item4 Attr4

fiddle

Upvotes: 0

Error_2646
Error_2646

Reputation: 3801

The other answer is good. This way is a bit more explicit.

Rationale - NOT EXISTS to exclude all items for which there is a record with 0 in both Col1 and Col2.

WITH t(Item,Col1,Col2,Attr) AS
(
  SELECT 'Item1' , 1 , 0 , 'Attr1' FROM dual UNION ALL
  SELECT 'Item1' , 1 , 1 , 'Attr2' FROM dual UNION ALL
  SELECT 'Item1' , 0 , 0 , 'Attr3' FROM dual UNION ALL
  SELECT 'Item2' , 1 , 0 , 'Attr2' FROM dual UNION ALL
  SELECT 'Item2' , 0 , 1 , 'Attr1' FROM dual UNION ALL
  SELECT 'Item3' , 1 , 1 , 'Attr4' FROM dual UNION ALL
  SELECT 'Item4' , 0 , 1 , 'Attr4' FROM dual UNION ALL
  SELECT 'Item4' , 1 , 1 , 'Attr4' FROM dual 
)
SELECT distinct
       t1.item,
       t1.attr
  FROM t t1
 where not exists
         (
           select 1
             from t t2
            where t1.Item = t2.item
              and t2.col1 = 0
              and t2.col2 = 0
         )
 order
    by t1.item,
       t1.attr;

Upvotes: 0

Barbaros Özhan
Barbaros Özhan

Reputation: 65363

A PL/SQL block is not needed but just use the following SELECT statement containing a HAVING clause and a self-join in order to return the extra column like the one below :

SELECT t1.Item, t2.Attr
  FROM t t1
  JOIN t t2
    ON t1.Item = t2.Item
 GROUP BY t1.Item, t2.Attr
 HAVING MIN(t1.Col1+t1.Col2)>0

Demo

P.S. All values of Col1 and Col2 columns are assumed to be non-null integers as stated within the example dataset.

Upvotes: 0

Related Questions