CODEWITHSUNDEEP
sqloracle-database
dsbalaban
dsbalaban

Reputation: 43

Trying to aggregate string column by choosing 1 of several values

I have a dataset that I want to aggregate based on a string column. Dataset is basically:

system      status
-------------------
PRE1-SYS1   SUCCESS
PRE1-SYS2   SUCCESS
PRE2-SYS1   RUNNING
PRE2-SYS2   SUCCESS
PRE3-SYS1   SUCCESS
PRE3-SYS2   <blank>

Basically, I want this to become:

system      status
-------------------
PRE1    SUCCESS
PRE2    RUNNING
PRE3    RUNNING

I have the sql needed to trim down the system values to PRE1, and PRE2, but I'm not sure how to aggregate the string function so that a system is:

I've looked at LISTAGG but I don't think it applies.

Upvotes: 1

Views: 66

Answers (4)

Justin Cave
Justin Cave

Reputation: 231781

Another alternative. Practically, this ends up being very similar to @trincot's solution, I'm just separating the logic for getting the counts from the logic that interprets those counts. If your logic gets more complicated in the future, this may be a bit more flexible.

with
     inputs ( system, status ) as (
       select 'PRE1-SYS1', 'SUCCESS' from dual union all
       select 'PRE1-SYS2', 'SUCCESS' from dual union all
       select 'PRE2-SYS1', 'RUNNING' from dual union all
       select 'PRE2-SYS2', 'SUCCESS' from dual union all
       select 'PRE3-SYS1', 'SUCCESS' from dual union all
       select 'PRE3-SYS2',  ''       from dual
     ),
     /* The cnts CTE counts how many rows relate to a SYSTEM, 
        how many of those are SUCCESS, and how many are NULL.
      */
     cnts( system, num_rows, num_success, num_null ) as (
       select substr(system,1,instr(system, '-')-1) system,
              count(*),
              sum(case when status = 'SUCCESS' then 1 else 0 end),
              sum(case when status is null then 1 else 0 end)
        from inputs
       group by substr(system,1,instr(system, '-')-1)
     )
/* Using the counts from the CTE, we can implement whatever logic we
   want
 */
select system,
       (case when num_rows = num_success then 'SUCCESS'
             when num_rows = num_null then 'PENDING'
             else 'RUNNING'
         end) status
  from cnts

Upvotes: 0

M T Head
M T Head

Reputation: 1290

I would approach it by ranking the responses. For example set a value to the most desired to the least desired results:

SUCCESS = 1
RUNNING = 2
<blank> = 3
PENDING = 3

Then select a min based on that.

select xval = case status when 'Success' then 1
                          when 'Running' then 2
                          when 'Pending' then 3
                                         else 3
                          end

Use a nested sub select on the value you get here so you only get one record per system. select System, Min(Xval) 

then display the 1 as Success
             the 2 as Running
             and 3 as Pending

It is hard to do in text format, easier to do with numbers. The numbers you assign to the string are important because they determine when you have multiple values which single one you return in your final query.

Upvotes: 0

user5683823
user5683823

Reputation: 

with
     inputs ( system, status ) as (
       select 'PRE1-SYS1', 'SUCCESS' from dual union all
       select 'PRE1-SYS2', 'SUCCESS' from dual union all
       select 'PRE2-SYS1', 'RUNNING' from dual union all
       select 'PRE2-SYS2', 'SUCCESS' from dual union all
       select 'PRE3-SYS1', 'SUCCESS' from dual union all
       select 'PRE3-SYS2',  ''       from dual
     ),
     prep ( system, flag ) as (
       select substr(system, 1, instr(system, '-') - 1),
              case status when 'SUCCESS' then 0 
                          when 'RUNNING' then 1 else 2 end
       from   inputs
     )
select system,
       case when min(flag) = 2 then 'PENDING'
            when max(flag) = 0 then 'SUCCESS'
            else                    'RUNNING' end as status
from prep
group by system
order by system;

Output:

SYSTEM    STATUS
--------- -------
PRE1      SUCCESS
PRE2      RUNNING
PRE3      RUNNING

Upvotes: 2

trincot
trincot

Reputation: 350841

Here is an SQL query you could use:

select   regexp_substr(system, '^[^-]*') as prefix,
         case
            when count(status) = 0 then 'PENDING'
            when count(*) = count(case when status = 'SUCCESS' then 1 end) then 'SUCCESS'
            else 'RUNNING'
         end as status
from     mytable
group by regexp_substr(system, '^[^-]*')

Upvotes: 2

Related Questions