oracle query to fetch count

Question

I have a table with organisation names and approval_status. Approval_status can be either approved /rejected. How do I fetch count of approved and rejected records for each organisation using oracle SQL query and PLSQL procedure

Answer 1

For Oracle's SQL Select statement, One option would be using sum of decoded columns as below :

select organisation_name, 
       sum(decode(approval_status,'approved',1,0)) as "Count(Approved)",
       sum(decode(approval_status,'rejected',1,0)) as "Count(Rejected)"
  from tab
 group by organisation_name;

You can create a Stored Procedure returning these calculated values as results as :

SQL> set serveroutput on;
SQL> create or replace procedure pr_get_org_count( 
                                             i_org_name tab.organisation_name%type,
                                             o_cnt_approved out pls_integer,
                                             o_cnt_rejected out pls_integer,
                                            ) is

begin
     select sum(decode(approval_status,'approved',1,0)) ,
            sum(decode(approval_status,'rejected',1,0))
       into o_cnt_approved, o_cnt_rejected
       from tab
      where organisation_name = i_org_name;

    dbms_output.put_line(' Approved Count is : '||o_cnt_approved);
    dbms_output.put_line(' Rejected Count is : '||o_cnt_rejected);
    -- these previous two line can be used to show the results through command line.
end;

If you need to output multi-rows for each organisation, use the above-most grouped select statement within a cursor inside our procedure without parameters as :

SQL> create or replace procedure pr_get_org_count is    
begin
    for c in
    (  
     select organisation_name,
            sum(decode(approval_status,'approved',1,0)) as cnt_approved ,
            sum(decode(approval_status,'rejected',1,0)) as cnt_rejected
       from tab
      group by organisation_name
    )
    loop
      dbms_output.put(' Organisation Name : '||c.organisation_name);        
      dbms_output.put(' [ Approved Count : '||c.cnt_approved);
      dbms_output.put_line(' - Rejected Count : '||c.cnt_rejected||' ]');
    end loop;
end;

EDIT (related to the comment) You can convert this procedure to the style below to mail the results :

create or replace procedure pr_get_org_count( 
                                             i_org_name tab.organisation_name%type,
                                             o_cnt_approved out pls_integer,
                                             o_cnt_rejected out pls_integer,
                                            ) is
  v_path  varchar2(4000):='http://www.mycompany.com.tr/main/default.aspx?email=';
  v_email varchar2(4000):='barbaros.ozhan@mycompany.com.tr';
  v_text  varchar2(4000):='My Results | for Approved : ';
  v_url   varchar2(4000);
  v_rep   varchar2(4000);
begin
  select sum(decode(approval_status,'approved',1,0)) ,
         sum(decode(approval_status,'rejected',1,0))
    into o_cnt_approved, o_cnt_rejected
    from tab
   where organisation_name = i_org_name;

   v_text := v_text||to_char(o_cnt_approved)||' - for Rejected : '||to_char(o_cnt_rejected);
   v_url  := v_path||'?email='||v_email||'&email_text='||v_text;
   v_rep  := utl_http.request(utl_url.escape(v_url, false, 'UTF-8'));
end;

Answer 2

Here's one option using conditional aggregation with a case statement:

select organisation, 
       count(case when approval_status = 'approved' then 1 end) as approvedcount,
       count(case when approval_status = 'rejected' then 1 end) as rejectedcount
from yourtable
group by organisation