title: SQL different aggregates in single query
body: <p>I am trying to work out a query where I have a table representing the following:</p>
  
  <p><a href="https://i.stack.imgur.com/tdsgH.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/tdsgH.png" alt="enter image description here"></a></p>
  
  <p>and I need the result to indicate the earliest start time (blue), the latest end time (green), and the sum of the lunch breaks (yellow):</p>
  
  <p><a href="https://i.stack.imgur.com/uuFgu.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/uuFgu.png" alt="enter image description here"></a></p>
  
  <p>I got the blue and green blocks right, but are struggling with the yellow aggregation.</p>
  
  <p>My (partial) query looks like:</p>
  
  <pre><code>select
      Name,
      min(StartTime) StartTime,
      max(EndTime) EndTime,
      sum( &lt;please help here with alternative to this aggregation&gt; ) Lunch
  from
      SomeTable
  group by
      Name
  </code></pre>
  
  <p>When I use a normal subquery, SQL complains that the column is not contained in either a "group by" or aggregate, and I cannot use a subquery inside an aggregate.</p>
  
  <p>Please point me into a direction for the "lunch" column.</p>
  
  <p>This is on SQL Server.</p>