title: Oracle SQL group by cube with distinct ID
body: <p><strong>Example data</strong> (complete table has more columns and millions of rows):</p>
  
  <pre><code>invoice_number |year            |department      |euros
  -------------------------------------------------------------
  1234           |2010            |1               | 200
  1234           |2011            |1               | 200
  1234           |2011            |2               | 200           
  4567           |2010            |1               | 450
  4567           |2010            |2               | 450
  4567           |2010            |3               | 450
  </code></pre>
  
  <p><strong>My Objective:</strong></p>
  
  <p>I want to sum the euros for every year and every department in every possible combination.</p>
  
  <p><strong>How result should look:</strong></p>
  
  <pre><code>year             |department         |euros
  --------------------------------------------
  2010             |1                  |650
  2010             |2                  |450
  2010             |3                  |450
  2010             |(null)             |650
  2011             |1                  |200
  2011             |2                  |200
  (null)           |1                  |650
  (null)           |2                  |650
  (null)           |3                  |450
  (null)           |(null)             |650
  </code></pre>
  
  <p><strong>My query:</strong></p>
  
  <pre><code>select      year
  ,           department
  ,           sum(euros)
  from        table1
  group by    cube    (
                      year
              ,       department
                      )
  </code></pre>
  
  <p><strong>Problem:</strong></p>
  
  <p>One invoice number can occur in several categories. For example, one invoice can have items from 2010 and 2011. This is no problem when I want to show the data per year. However, when I want the total over all years the euros will be summed twice, one time for each year. I want the functionality of 'group by cube' but I want to sum only distinct invoice numbers for aggregations.</p>
  
  <p><strong>Problem table:</strong></p>
  
  <pre><code>year             |department         |euros
  --------------------------------------------
  2010             |1                  |650
  2010             |2                  |450
  2010             |3                  |450
  2010             |(null)             |1550
  2011             |1                  |200
  2011             |2                  |200
  (null)           |1                  |850
  (null)           |2                  |650
  (null)           |3                  |450
  (null)           |(null)             |1950
  </code></pre>
  
  <p>Is it possible to do what I want? So far my search has yielded no results. I have created a <a href="http://sqlfiddle.com/#!4/66fe2/2/0" rel="nofollow">SQL Fiddle</a>, I hope it works</p>