doing a group by within a group

Question

i have two columns business_line(with values X,Y) and business_segment(values X,Y,Z same X and Y as business_line) in a table with name "Sometable". I have another column with name type_of_payment(with values A,B,C,D,E) and a final column with name transaction_value. This is what i want to do:

Sum the transactions grouped by business_line and business_segment and also find out what proportion of these payments were from A,C,E. So my output table would be something like this

                                             (last three columns can be named anything
                                              but they specify proportions of A,C,E)  
Business_line       SUM(transaction_value)   A        C        E
and business seg.
X                   100                      20%      30%      50%
Y                   200                      11%      12%      77%     
X                   300                        and so on
Y                   170
Z                   230

How do i do this??

PS : the sums of A C E need not be 100% as B and D are also present

Answer 1

For Oracle 11g and above, you can use PIVOT

select *
from
(
select sometable.line, paymenttype,total, 100.0*transaction_value/total as percentage
from sometable
    inner join
    (select line, sum(transaction_value) as total
        from sometable
        group by line) total
    on sometable.line = total.line          
) 
pivot 
(
    sum(percentage) for paymenttype in (a,c,e)
) 

Answer 2

This is standard SQL, should work on Oracle (but untested):

SELECT
   business_line,
   business_segment,
   grand_total,
   A_total * 100.0 / grand_total as A,
   C_total * 100.0 / grand_total as C,
   E_total * 100.0 / grand_total as E
FROM
   (
       SELECT
          business_line,
          business_segment,
          SUM(transaction_value) as grand_total,
          SUM(CASE WHEN payment_type = 'A' THEN transaction_value END) as A_total,
          SUM(CASE WHEN payment_type = 'C' THEN transaction_value END) as C_total,
          SUM(CASE WHEN payment_type = 'E' THEN transaction_value END) as E_total
       FROM
          SomeTable
       GROUP BY
          business_line,
          business_segment
    ) as t