title: How to use group by with pivot in Oracle 11g
body: <p>Table Structure</p>
  
  <pre><code>CREATE TABLE PLAN 
    ( 
       plan_id   NUMERIC, 
       startdate DATE NOT NULL, 
       enddate   DATE, 
       cost      NUMERIC (3) NOT NULL, 
       plan_type VARCHAR (5) NOT NULL, 
       CONSTRAINT plan_pk PRIMARY KEY (plan_id), 
       CONSTRAINT plan_ck CHECK (startdate &lt;= enddate) 
    );
  </code></pre>
  
  <p>Requirement:</p>
  
  <p>Create a pivot table to list the number of plans by plan type (voice, text, data) and also by cost range. For cost range, create 3 segments, “Economy Plans” defined as those with monthly fee less than $40, “Standard Plans” defined as plans with monthly fees between $40 and $50, and “Premium Plans” defined as those with monthly fees more than $50. The pivot table result should include 3 columns (Voice, Data, Text) and 3 rows (Economy Plans, Standard Plans, Premium Plans). The cells should contain the counts of each and should have 0 rather than NULL where    there are no matching plans.</p>
  
  <p>MY query SO FAR</p>
  
  <pre><code>SELECT * FROM 
  (SELECT cost, plan_type
      FROM plan) 
  PIVOT ( count(plan_type) FOR plan_type IN ('voice', 'data', 'text')
  );
  </code></pre>
  
  <p>it return count of voice, data, text type plans for all cost. I am unable to get it in range as specified in the requirement.</p>
  
  <p>MY other try</p>
  
  <pre><code>SELECT CASE 
           WHEN cost &lt; 40 THEN '1-40' 
           WHEN cost&lt;=50 THEN '40-50' 
           ELSE '50+' 
         END AS cost, count('voice'), count('data'), count('text') FROM 
  (SELECT cost, plan_type
      FROM plan) 
  PIVOT ( 
  count(plan_type) FOR plan_type IN ('voice', 'data', 'text')
  )
  GROUP BY CASE 
           WHEN cost &lt; 40 THEN '1-40' 
           WHEN cost&lt;=50 THEN '40-50' 
           ELSE '50+' 
         END;
  </code></pre>
  
  <p>This query even count 1 for 0  value from pivot table thus returning a wrong answer. Please suggest how should I proceed about it? </p>