How to only pull example of a value when more than 50% of the results match-CodePudding

Code	Error	Warning
ABC	1	0
ABC	0	0
ABC	0	0
DEF	1	0
DEF	0	0
DEF	1	0
GHI	1	0
GHI	0	1

I need to be able to pull one value indicating that more than 50% of the values listed have a "1" in either the "error" or "warning" columns.

In this example, I would need to get back something like this:

Code
DEF
GHI

Since both "DEF" and "GHI" had more than 50% of their total rows with a "1" in either the "Error" or "Warning" column. "ABC" would not be included since it did not meet the criteria of 50%.

CodePudding user response：

If the values of error and warning are always either 1 or 0 your query can simply be:

select code
from main
group by code
having avg(error) >= .5
      or avg(warning) >= .5;

See it working here: https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=02cdf03753da82fe2d3686523165ff09

CodePudding user response：

with main as(
select 'ABC' as code, 1 as error, 0 as warning from dual
union all
select 'ABC' as code, 0 as error, 0 as warning from dual
union all
select 'ABC' as code, 0 as error, 0 as warning from dual
union all
select 'DEF' as code, 1 as error, 0 as warning from dual
union all
select 'DEF' as code, 0 as error, 0 as warning from dual
union all
select 'DEF' as code, 1 as error, 0 as warning from dual
union all
select 'GHI' as code, 1 as error, 0 as warning from dual
union all
select 'GHI' as code, 0 as error, 1 as warning from dual)

select 
    x.*,
    case
        when (code_error/code_total   code_warning/code_total) > 1/2 then 'show'
        else 'hide'
    end as isVisible    
from(
select distinct code,
    (select count(1) from main where code = x.code and error = 1) as code_error,
    (select count(1) from main where code = x.code and warning = 1) as code_warning,
    (select count(1) from main where code = x.code) as code_total
from main x)x

CodePudding user response：

This example works for any tables on DB:

with table1 as 
(
    select 1 as id, 'ABC' as code, 1 as error, 0 as warning from dual
    union all
    select 2 as id, 'ABC' as code, 0 as error, 0 as warning from dual
    union all
    select 3 as id, 'ABC' as code, 0 as error, 0 as warning from dual
    union all
    select 4 as id, 'DEF' as code, 1 as error, 0 as warning from dual
    union all
    select 5 as id, 'DEF' as code, 0 as error, 0 as warning from dual
    union all
    select 6 as id, 'DEF' as code, 1 as error, 0 as warning from dual
    union all
    select 7 as id, 'GHI' as code, 1 as error, 0 as warning from dual
    union all
    select 8 as id, 'GHI' as code, 0 as error, 1 as warning from dual
)
select 
    mm.* 
from (
    select 
        t1.*, 
        count(1) over () as rowcount,
        ROW_NUMBER() OVER(
            ORDER BY id
        ) as rnum 
    from table1 t1 
) mm 
where mm.rnum > (mm.rowcount/2)

CodePudding user response：

You can use:

SELECT code
FROM   table_name
GROUP BY code
HAVING AVG(GREATEST(Error, Warning)) >= 0.5;

Which, for the sample data:

CREATE TABLE table_name (Code, Error, Warning) AS
SELECT 'ABC', 1, 0 FROM DUAL UNION ALL
SELECT 'ABC', 0, 0 FROM DUAL UNION ALL
SELECT 'ABC', 0, 0 FROM DUAL UNION ALL
SELECT 'DEF', 1, 0 FROM DUAL UNION ALL
SELECT 'DEF', 0, 0 FROM DUAL UNION ALL
SELECT 'DEF', 1, 0 FROM DUAL UNION ALL
SELECT 'GHI', 1, 0 FROM DUAL UNION ALL
SELECT 'GHI', 0, 1 FROM DUAL UNION ALL
SELECT 'JKL', 1, 0 FROM DUAL UNION ALL
SELECT 'JKL', 0, 0 FROM DUAL UNION ALL
SELECT 'JKL', 0, 1 FROM DUAL UNION ALL
SELECT 'JKL', 0, 0 FROM DUAL;

Outputs:

CODE

DEF

GHI

JKL

db<>fiddle here