title: COUNT in PLSQL ORACLE
body: <p>I have asked this question before but I did not get any help.
   I want to get the count of rows in two different table given an attribute.
   This is my code .
   Instead of fetching the total count where the condition holds, I am getting the whole count of the table </p>
  
  <pre><code>    create or replace PROCEDURE p1( suburb IN varchar2 ) 
      as
          person_count NUMBER;
          property_count NUMBER;
      BEGIN
          SELECT count(*) INTO person_count
          FROM person p WHERE p.suburb = suburb ;
  
          SELECT count(*) INTO property_count
          FROM property pp WHERE pp.suburb = suburb ;
  
  
          dbms_output.put_line('Number of People :'|| person_count);
          dbms_output.put_line('Number of property :'|| property_count);
  
      END;
      /
  </code></pre>
  
  <p>Is there any other way to do this so that i can retrieve the real total count of people in that SUBURB</p>
  
  <p>Some datas from PERSON TABLE</p>
  
  <pre><code>       PEID FIRSTNAME       LASTNAME
      ---------- -------------------- --------------------
      STREET                   SUBURB           POST TELEPHONE
      ---------------------------------------- -------------------- ---- ------------
          30 Robert       Williams
      1/326 Coogee Bay Rd.             Coogee           2034 9665-0211
  
          32 Lily         Roy
     66 Alison Rd.                 Randwick         2031 9398-0605
  
          34 Jack         Hilfgott
      17 Flood St.                 Bondi            2026 9387-0573
  </code></pre>
  
  <p>SOME DATA from PROPERTY TABLE</p>
  
  <pre><code>          PNO STREET                    SUBURB       POST
      ---------- ---------------------------------------- -------------------- ----
      FIRST_LIS TYPE               PEID
      --------- -------------------- ----------
          48 66 Alison Rd.                Randwick         2031
      12-MAR-11 Commercial            8
  
          49 1420 Arden St.               Clovelly         2031
      27-JUN-10 Commercial               82
  
          50 340 Beach St.                Clovelly         2031
      05-MAY-11 Commercial               38
  </code></pre>
  
  <p>Sorry for the way the table is looking .</p>
  
  <p>This is the value I get when I run the above script.</p>
  
  <pre><code>     SQL&gt; exec p1('Randwick')
       Number of People :50
       Number of property :33
  </code></pre>
  
  <p>I changed the PROCEDURE ,this is what I get .</p>
  
  <pre><code>    SQL&gt; create or replace PROCEDURE p1( location varchar2 ) 
      IS
          person_count NUMBER;
          property_count NUMBER;
      BEGIN
          SELECT count(p.peid) INTO person_count
          FROM person p WHERE p.suburb = location ;
  
          SELECT count(pp.pno) INTO property_count
          FROM property pp WHERE pp.suburb = location ;
  
  
          dbms_output.put_line('Number of People :'|| person_count);
          dbms_output.put_line('Number of property :'|| property_count);
  
      END;
      /
        2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17  
      Procedure created.
  
      SQL&gt; exec p1('KINGSFORD')
      Number of People :0
      Number of property :0
  
      PL/SQL procedure successfully completed.
  
      SQL&gt; 
  
  
      SQL&gt; 
      SQL&gt; exec p1('Randwick')
      Number of People :0
      Number of property :0
  
      PL/SQL procedure successfully completed.
  
      SQL&gt; 
  </code></pre>
  
  <p>The solution suppose to be this </p>
  
  <pre><code>    SQL&gt; exec p1('randwick');
      Number of People: 7
      Number of Property: 2
  </code></pre>