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

What are your expected results? Both options below work, they just output the results differently. — sgeddes
– sgeddes, Commented Dec 13, 2018 at 18:54

sgeddes · Accepted Answer · 2018-12-13 18:47:42Z

1

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

answered Dec 13, 2018 at 18:47

sgeddes

62.9k7 gold badges68 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

newlearner Over a year ago

I would want to extract this as a report and send over email on a monthly basis. Can I use this query inside a PLSQL procedure. Please help with the process

Barbaros Özhan · Accepted Answer · 2018-12-15 10:01:34Z

0

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):='[email protected]';
  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;

edited Dec 15, 2018 at 10:01

answered Dec 13, 2018 at 19:04

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

6 Comments

newlearner Over a year ago

Hi, Yes that helps... I want to send this o/p as a report over email to some email id's. In the same stored procedure can I do that..

newlearner Over a year ago

for the first stored procedure... i am executing this in TOAD .. please help me understand how i can display the output in a table kind format

newlearner Over a year ago

this gives o/p in a one liner... There are multiple organisations. For each organisation I would need the Approved records and rejected records count respectively. In a table format. The headers should be ORG_NAME APPROVED REJECTED ... below this each org name should display with respective counts. I am unable to give you a picture to show the o/p format. Hope u got my point. And many thanks for helping me learn this concepts

Barbaros Özhan Over a year ago

@newlearner exactly, that's another option to output multi-organization which needs a cursor as you've said.

newlearner Over a year ago

Now if I want to display it in a tabular format like how we have data in an excel sheet. i want to have a table created with borders and 3 columns -- ORG_NAME, APPROVED, REJECTED. These should be the columns and as per the number of rows returned I want to have rows below each. Is that possible and This table format should go as body in an email to few people.

|

Collectives™ on Stack Overflow

oracle query to fetch count

2 Answers 2

1 Comment

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related