Display Total, Subtotal, Percentage in Oracle query

oracle10g, sql

Solution

select subtotal, 
       total,
       category, 
       year,
       subtotal / total * 100 as percentage
from (
  select count(*) over (partition by category, year) as subtotal,
         count(*) over (partition by year) as total,
         category,
         year
  from the_unknown_table
) t
order by year;

Problem

Question, I have table like: ``` PID Category Year 1 AAA 2011 2 AAA 2012 3 BBB 2011 4 CCC 2010 5 CCC 2011 6 CCC 2012 ``` I need to display Output as: ``` Subtotal Total Category Year Percentage 1 1 CCC 2010 100% 1 2 AAA 2011 50% 1 2 BBB 2011 50% 1 2 AAA 2012 50% 1 2 CCC 2012 50% ``` Where subtotal is count of that acategory for a particular year. Total is count for a particular year including all category. Percentage is Subtotal/total *100

Original source