How to get the percentage of total in a column and group by accordingly in PLsql

Question

I have a table XXX as follows.

And I need against Load Id, the total number of Load Item Ids, total number of errors, and errors as a percentage.

So for the given example output should be as follows;

Any idea how should I get this?

Thanks

Answer 1

Conditional aggregation is one option.

Sample data:

SQL> with xxx (load_item_id, load_id, cb_error) as
  2    (select 0, 105, 1 from dual union all
  3     select 1, 105, 0 from dual union all
  4     select 2, 105, 1 from dual union all
  5     select 3, 105, 0 from dual union all
  6     select 4, 105, 1 from dual union all
  7     select 5, 105, 0 from dual union all
  8     --
  9     select 1, 113, 1 from dual union all
 10     select 0, 113, 1 from dual
 11    )

Query begins here:

 12  select load_id, count(*) total,
 13    sum(case when cb_error = 1 then 1 else 0 end) errors,
 14    --
 15    round(sum(case when cb_error = 1 then 1 else 0 end) / count(*) * 100) error_pct
 16  from xxx
 17  group by load_id
 18  order by load_id;

   LOAD_ID      TOTAL     ERRORS  ERROR_PCT
---------- ---------- ---------- ----------
       105          6          3         50
       113          2          2        100

SQL>