title: How do I use GROUP BY in SQL Server 2012
body: <p>I've got two tables, one contains the list of bins and the second contains the weekdays where that bin is collected.</p>
  <pre><code>declare @bins table (
      id int IDENTITY(1,1) PRIMARY KEY,
      name nvarchar(255),
      collectionCode nvarchar(255)
  )
  
  declare @collectionDays table (
      id int IDENTITY(1,1) PRIMARY KEY,
      weekday int,
      collectionCode nvarchar(255)
  )
  
  insert into @bins (name, collectionCode) values
  ('Bin 1','MWF'),
  ('Bin 2','MWF'),
  ('Bin 3','ED'),
  ('Bin 4','ED'),
  ('Bin 5','ED'),
  ('Bin 6','ED'),
  ('Bin 7','ED'),
  ('Bin 8','ED'),
  ('Bin 9','ED'),
  ('Bin 10','MWF')
  
  insert into @collectionDays (weekday, collectionCode) values
  (0,'MWF'),
  (2,'MWF'),
  (4,'MWF'),
  (0,'ED'),
  (1,'ED'),
  (2,'ED'),
  (3,'ED'),
  (4,'ED'),
  (5,'ED'),
  (6,'ED')
  </code></pre>
  <p>What I want to do is return list of all the bins with their next collection day.</p>
  <p>I've already created this query where if returns the next collection date but it only returns the next collection date for just one bin at a time. I don't want to run this query for each bin in the database.</p>
  <p>Here's my query</p>
  <pre><code>select top 1
      name,
      format(dateadd(day, (datediff(day, weekday, getdate()) / 7) * 7 + 7, weekday), 'dd/MM/yyyy') AS date
  from @bins b
  join @collectionDays c on b.collectionCode = c.collectionCode
  where b.id = 1
  order by date asc
  </code></pre>
  <p>If I remove <code>top 1</code> and <code>b.id = 1</code> condition, it'll return all the bins and next date for each weekday. If I try using <code>group by</code>, I get an error <code>Column '@collectionDays.weekday' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.</code></p>
  <pre><code>select
      name,
      format(dateadd(day, (datediff(day, weekday, getdate()) / 7) * 7 + 7, weekday), 'dd/MM/yyyy') AS date
  from @bins b
  join @collectionDays c on b.collectionCode = c.collectionCode
  group by b.id
  order by date asc
  </code></pre>
  <p>Any ideas on how I can return the next collection date for each bin in a single query?</p>
  <p>EDIT: Updated the queries with join and other stuff</p>