title: T-SQL: Two Level Aggregation in Same Query
body: <p>I have a query that joins a master and a detail table. Master table records are duplicated in results as expected. I get aggregation on detail table an it works fine. But I also need another aggregation on master table at the same time. But as master table is duplicated, aggregation results are duplicated too. </p>
  
  <p>I want to demonstrate this situation as below;</p>
  
  <pre><code>    If Object_Id('tempdb..#data') Is Not Null Drop Table #data
      Create Table #data (Id int, GroupId int, Value int)
  
      If Object_Id('tempdb..#groups') Is Not Null Drop Table #groups
      Create Table #groups (Id int, Value int)
  
      /* insert groups */
      Insert #groups (Id, Value)
      Values  (1,100), (2,200), (3, 200)
  
  
      /* insert data */
      Insert #data (Id, GroupId, Value)
      Values  (1,1,10),
              (2,1,20),
              (3,2,50),
              (4,2,60),
              (5,2,70),
              (6,3,90)
  </code></pre>
  
  <p>My select query is</p>
  
  <pre><code>Select  Sum(data.Value) As Data_Value,
          Sum(groups.Value) As Group_Value
  From    #data data
          Inner Join #groups groups On groups.Id = data.GroupId
  </code></pre>
  
  <p>The result is;</p>
  
  <pre><code>Data_Value  Group_Value
  300         1000
  </code></pre>
  
  <p>Expected result is;</p>
  
  <pre><code>Data_Value  Group_Value
  300         500
  </code></pre>
  
  <p>Please note that, derived table or sub-query is not an option. Also <code>Sum(Distinct groups.Value)</code> is not suitable for my case.</p>