title: Pivot in SQL: count not working as expected
body: <p>I have in my Oracle Responsys Database a table that contains records with amongst other two variables: </p>
  
  <ul>
  <li><p>status</p></li>
  <li><p>location_id</p></li>
  </ul>
  
  <p>I want to count the number of records grouped by status and location_id, and display it as a pivot table.</p>
  
  <p>This seems to be the exact example that appears <a href="https://www.techonthenet.com/oracle/pivot.php" rel="nofollow noreferrer">here</a></p>
  
  <p>But when I use the following request : </p>
  
  <pre><code>select * from 
      (select status,location_id from $a$ ) 
          pivot (count(status) 
          for location_id in (0,1,2,3,4)
      ) order by status
  </code></pre>
  
  <p>The values that appear in the pivot table are just the column names :</p>
  
  <p>output : </p>
  
  <pre><code>status    0    1    2    3    4
  -1        0    1    2    3    4
  1         0    1    2    3    4
  2         0    1    2    3    4
  3         0    1    2    3    4
  4         0    1    2    3    4
  5         0    1    2    3    4
  </code></pre>
  
  <p>I also gave a try to the following : </p>
  
  <pre><code>select * from 
       (select status,location_id , count(*) as nbreports 
         from $a$ group by status,location_id ) 
                pivot (sum(nbreports) 
                for location in (0,1,2,3,4)
      ) order by status
  </code></pre>
  
  <p>but it gives me the same result.</p>
  
  <pre><code> select status,location_id , count(*) as nbreports 
   from $a$ 
   group by status,location_id
  </code></pre>
  
  <p>will of course give me the values I want, but displaying them as a column and not as a pivot table</p>
  
  <p><strong>How can I get the pivot table to have in each cell the number of records with the status and location in row and column?</strong></p>
  
  <p>Example data: </p>
  
  <pre><code>CUSTOMER,STATUS,LOCATION_ID
  1,-1,1
  2,1,1
  3,2,1
  4,3,0
  5,4,2
  6,5,3
  7,3,4
  </code></pre>
  
  <p>The table data types :</p>
  
  <pre><code>CUSTOMER    Text Field (to 25 chars)
  STATUS  Text Field (to 25 chars)
  LOCATION_ID Number Field
  </code></pre>