CREATE TABLE die_roll (   
  die_value    NUMBER   
)

INSERT   
INTO   die_roll   
  SELECT rownum   
  FROM   dual   
  CONNECT BY rownum <= 6

COMMIT

WITH roll_the_dice AS (  
  SELECT     a1.die_value AS attacker_die_1,  
             a2.die_value AS attacker_die_2,  
             a3.die_value AS attacker_die_3,  
             d1.die_value AS defender_die_1,  
             d2.die_value AS defender_die_2,  
             rownum AS dice_combination  
  FROM       die_roll a1  
  CROSS JOIN die_roll a2  
  CROSS JOIN die_roll a3  
  CROSS JOIN die_roll d1  
  CROSS JOIN die_roll d2  
), unpiv AS (  
  SELECT  *  
  FROM    roll_the_dice  
  UNPIVOT (die_value FOR die_name IN (attacker_die_1, attacker_die_2, attacker_die_3, defender_die_1, defender_die_2))  
), order_dice AS (  
  SELECT dice_combination, SUBSTR(die_name, 1, 8) AS die_owner, die_value,  
         ROW_NUMBER() OVER (PARTITION BY dice_combination, SUBSTR(die_name, 1, 8) ORDER BY die_value DESC, die_name) AS rn  
  FROM   unpiv  
), piv AS (  
  SELECT *  
  FROM   order_dice  
  PIVOT  (MAX(die_value) die_value FOR rn IN (1 first, 2 second))  
), identify_battle_outcome AS (  
  SELECT --dice_combination, a.first_die_value attacher_die_1, a.second_die_value attacher_die_2,  
         --d.first_die_value defender_die_1, a.second_die_value defender_die_2,  
         CASE WHEN a.first_die_value  > d.first_die_value  THEN 1 ELSE 0 END +  
         CASE WHEN a.second_die_value > d.second_die_value THEN 1 ELSE 0 END AS attacker_wins  
  FROM   piv a  
  JOIN   piv d  USING (dice_combination)  
  WHERE  a.die_owner = 'ATTACKER'  
  AND    d.die_owner = 'DEFENDER'  
)  
SELECT attacker_wins, (2-attacker_wins) AS defender_wins, count(*)  
FROM   identify_battle_outcome  
GROUP BY attacker_wins  
ORDER BY attacker_wins