Oracle SQL - Count number of appearance of text in a group by expression, if >= 1 text, else another_text

user_9123228 0
Viewed 193

I want to make a select statement to have an overview of the status of each product. The status of a product can either be OK or NOT_OK and a product can be tested multiple times. I want to have the "worst" status for each product: I want to have NOT_OK if the product has already been tested at least once as NOT_OK and else OK.

Here is a little data sample:

PRODUCT | STATUS    | DATA_PRODUCT_SPECIFIC_TO_KEEP
--------+-----------+--------------------------------
A       | NOT_OK    | AAA
A       | OK        | AAA
B       | OK        | BBB
B       | OK        | BBB
B       | OK        | BBB
C       | NOT_OK    | CCC

Here is the result I expect to have:

PRODUCT | STATUS    | DATA_PRODUCT_SPECIFIC_TO_KEEP
--------+-----------+--------------------------------
A       | NOT_OK    | AAA
B       | OK        | BBB
C       | NOT_OK    | CCC

I tried to use the query below:

SELECT PRODUCT, count(STATUS="NOT_OK"), DATA_PRODUCT_SPECIFIC_TO_KEEP
FROM TABLE 
GROUP BY PRODUCT

but this is rejected by the error

missing right parenthesis

Do you have any idea?

aggregate group-by oracle sql
4 Answers

You should use a selective sum using case for check the right values 

SELECT PRODUCT, 
       sum(case  STATUS="NOT_OK" then 1 else  0 END), 
       DATA_PRODUCT_SPECIFIC_TO_KEEP
FROM TABLE 
GROUP BY PRODUCT, DATA_PRODUCT_SPECIFIC_TO_KEEP

and your group by must include all the columns not involved in aggreagation function

user_35223120

In Oracle, you need a case expression there. But you also need to fix the GROUP BY:

SELECT PRODUCT, SUM(CASE WHEN STATUS = 'NOT_OK' THEN 1 ELSE 0 END) as not_ok_cnt, 
       DATA_PRODUCT_SPECIFIC_TO_KEEP
FROM TABLE 
GROUP BY PRODUCT, DATA_PRODUCT_SPECIFIC_TO_KEEP;

All non-aggregated columns need to be in the GROUP BY.

user_11440350

To get that result, you seem to need a MIN more than a COUNT; with your data, this

select product, min(status), data_product_specific_to_keep
from yourtable
group by product, data_product_specific_to_keep

gives:

A       NOT_OK      AAA                          
B       OK          BBB                          
C       NOT_OK      CCC

If you need to handle more than two values, you have to define a priority logic; you can implement it with something like the following:

select PRODUCT,
       case (status)
           when 2 then 'NOT_OK'
           when 1 then 'AWAIT'
           else 'OK'
       end,
       DATA_PRODUCT_SPECIFIC_TO_KEEP
from (
        select min(
                   case (status)
                     when 'NOT_OK' then 2
                     when 'AWAIT' then 1
                     else 0
                   end
                  ) as status,
               PRODUCT, DATA_PRODUCT_SPECIFIC_TO_KEEP
        from yourTable
        group by PRODUCT, DATA_PRODUCT_SPECIFIC_TO_KEEP
)
order by 1

With a table like this

create table yourTable (PRODUCT, STATUS, DATA_PRODUCT_SPECIFIC_TO_KEEP) as (
    select 'A', 'NOT_OK', 'AAA' from dual union all
    select 'A', 'OK', 'AAA' from dual union all
    select 'B', 'OK', 'BBB' from dual union all
    select 'B', 'AWAIT', 'BBB' from dual union all
    select 'B', 'OK', 'BBB' from dual union all
    select 'C', 'NOT_OK', 'CCC' from dual union all
    select 'C', 'AWAIT', 'CCC' from dual union all
    select 'D', 'NOT_OK', 'DDD' from dual union all
    select 'D', 'NOT_OK', 'DDD' from dual
)

the result would be 

P CASE(S DAT
- ------ ---
A OK     AAA
B OK     BBB
C AWAIT  CCC
D NOT_OK DDD
user_57264670

Since you only have two status values... that will do the trick !

SELECT t.PRODUCT, MIN(t.STATUS), t.DATA_PRODUCT_SPECIFIC_TO_KEEP
  FROM yourTable t
 GROUP BY t.PRODUCT, t.DATA_PRODUCT_SPECIFIC_TO_KEEP

That will extract the minimum string value for each product, and "N" happens to be less than "O" in alphabetical order.

user_7814290
Related