title: how to group by on outer references or any alternative way?
body: <p>I am passing in two dates and need to group by the results by these two dates, but cannot do it as I get the error</p>
  
  <blockquote>
    <p>Msg 164, Level 15, State 1, Line 24 Each GROUP BY expression must
    contain at least one column that is not an outer reference.</p>
  </blockquote>
  
  <p>the query is as follows:</p>
  
  <pre><code>declare @sd datetime ='2012-07-01 00:00:00.000' ,
          @ed datetime ='2012-09-30 00:00:00.000' ;
  
  select @sd,@ed, 
          count(i.id)as count,
          sum(case when oi.rating &lt;50 then 1 else 0 end) as unfav,
          sum(case when oi.Rating =50  then 1 else 0 end) as neu,
          sum(case when oi.Rating &gt;50  then 1 else 0 end) as fav,
          avg(oi.Rating)as 'Av Rating'
          from Items i (nolock)
          inner join ItemOrganisations oi (nolock) on i.ID= oi.ItemID
          inner join Lookup_ItemTypes it (nolock) on it.ID = i.ItemTypeID
  
          inner join Batches b (nolock) on b.ID=i.BatchID
          inner join Lookup_ItemStatus lis (nolock) on lis.ID = i.StatusID
          inner join Lookup_BatchStatus lbs (nolock) on lbs.ID = b.StatusID
          inner join Lookup_BatchTypes bt on bt.id = b.Typeid
  
          where lbs.Name = 'Completed by Analyst' or lbs.Name='Delivered/Imported into Neptune Online'
          and lis.Name = 'Complete'
          and i.IsRelevant = 1
          and bt.Name = 'Live'
          group by @sd,@ed
          having i.OverrideDate between @sd and @ed
  </code></pre>
  
  <p>If I do not group by this is the result I get which is wrong:</p>
  
  <pre><code>2011-01-01 00:00:00.000 2011-01-31 00:00:00.000 1   0   0   1   55
  2011-01-01 00:00:00.000 2011-01-31 00:00:00.000 7   1   0   1   50
  2011-01-01 00:00:00.000 2011-01-31 00:00:00.000 7   1   0   0   20
  2011-01-01 00:00:00.000 2011-01-31 00:00:00.000 1   0   0   0   NULL
  2011-01-01 00:00:00.000 2011-01-31 00:00:00.000 8   1   0   6   66
  2011-01-01 00:00:00.000 2011-01-31 00:00:00.000 1   1   0   0   10
  2011-02-01 00:00:00.000 2011-02-28 00:00:00.000 1   0   0   1   55
  2011-02-01 00:00:00.000 2011-02-28 00:00:00.000 7   1   0   1   50
  2011-02-01 00:00:00.000 2011-02-28 00:00:00.000 7   1   0   0   20
  2011-02-01 00:00:00.000 2011-02-28 00:00:00.000 1   0   0   0   NULL
  2011-02-01 00:00:00.000 2011-02-28 00:00:00.000 8   1   0   6   66
  2011-02-01 00:00:00.000 2011-02-28 00:00:00.000 1   1   0   0   10
  2011-03-01 00:00:00.000 2011-03-31 00:00:00.000 1   0   0   1   55
  2011-03-01 00:00:00.000 2011-03-31 00:00:00.000 7   1   0   1   50
  2011-03-01 00:00:00.000 2011-03-31 00:00:00.000 7   1   0   0   20
  2011-03-01 00:00:00.000 2011-03-31 00:00:00.000 1   0   0   0   NULL
  2011-03-01 00:00:00.000 2011-03-31 00:00:00.000 8   1   0   6   66
  2011-03-01 00:00:00.000 2011-03-31 00:00:00.000 1   1   0   0   10
  2011-04-01 00:00:00.000 2011-04-30 00:00:00.000 1   0   0   1   55
  2011-04-01 00:00:00.000 2011-04-30 00:00:00.000 7   1   0   1   50
  2011-04-01 00:00:00.000 2011-04-30 00:00:00.000 7   1   0   0   20
  </code></pre>