title: SQL order groups by group subtotal
body: <p>I have data like this:</p>
  
  <pre><code>SaleID    ParentCo    Subsidiary      Amount
  1         BigCo       Big A LTD       10
  2         MedCo       Medium Inc.     10
  3         BigCo       BigCo Corp.     10
  4         BigCo       Biggest Co.     10
  5         BigCo       Big A LTD       10
  6         MedCo       Medium LLC      10
  7         MedCo       Medium Inc.     10
  8         SmallCo     SmallCo         10
  </code></pre>
  
  <p>I would like to display totals for each ParentCo and each Subsidiary, ordered DESC by ParentCo and nestedly (is that a word?) grouped and ordered by Subsidiary, like this:</p>
  
  <pre><code>ParentCo     Subsidiary    SumOfAmount
  BigCo        (all)         40
  BigCo        Big A LTD     20
  BigCo        BigCo Corp    10
  BigCo        Biggest Co.   10
  MedCo        (all)         30
  MedCo        Medium Inc.   20
  MedCo        Medium LLC    10
  SmallCo      (all)         10
  SmallCo      SmallCo       10
  </code></pre>
  
  <p>The farthest I have gotten is this:</p>
  
  <pre><code>SELECT [ParentCo], [Subsidiary], SUM([Amount]) AS SumOfAmount FROM [table]
  GROUP BY [ParentCo], [Subsidiary]
  WITH ROLLUP
  ORDER BY SumOfAmount DESC;
  </code></pre>
  
  <p>But as anybody who knows anything will see, that doesn't work because it displays all rows descending by SumOfAmount, without keeping the totals for each subsidiary aligned below (or for that matter, above or next to) the total for its ParentCo. </p>
  
  <p>I am thinking I probably need some kind of nested query, but then, I am a noob and the above is the most complex SQL I have ever written.</p>
  
  <p>In case it is easier, it would work just as well for me to display the sums for subsidiaries and parents in separate columns, repeating the parent's sum next to each subsidiary:</p>
  
  <pre><code>ParentCo     Subsidiary    SumOfSubsidiary   SumOfParent
  BigCo        Big A LTD     20                40
  BigCo        BigCo Corp    10                40
  BigCo        Biggest Co.   10                40
  MedCo        Medium Inc.   20                30
  MedCo        Medium LLC    10                30
  SmallCo      SmallCo       10                10
  </code></pre>
  
  <p>And while we are at it, a related question:</p>
  
  <p>Is there a way to display only the total for each ParentCo, but next to it, the name and subtotal for its <em>subsidiary with the largest subtotal</em>, like this:</p>
  
  <pre><code>ParentCo     SumOfParent    LargestSubsidiary  SumOfSubsidiary  
  BigCo        40             Big A LTD          20
  MedCo        30             Medium Inc.        20
  SmallCo      10             SmallCo            10
  </code></pre>
  
  <p>Any ideas? Thanks in advance!</p>