title: Group By with Min and Max Date using T-SQL
body: <p>I am trying to do some SQL on the data from the <a href="https://www.kaggle.com/benhamner/sf-bay-area-bike-share" rel="nofollow noreferrer">SF Bay Area Bike Share</a> Kaggel Data</p>
  
  <p>When I run this SQL:</p>
  
  <pre><code>/****** Select Bikes   ******/
  SELECT count(t.[id]) as TripCount
  ,t.[bike_id]
  FROM [dbo].[trip] t
  Group By t.[bike_id]
  having count(t.[id]) &lt; 25
  Order By TripCount asc
  </code></pre>
  
  <p>I get this result:</p>
  
  <pre><code>+-----------+---------+
  | TripCount | bike_id |
  +-----------+---------+
  |         6 |     876 |
  |        18 |     323 |
  |        20 |     565 |
  |        24 |     476 |
  |        24 |     697 |
  +-----------+---------+
  </code></pre>
  
  <p>Where what I really want is this:</p>
  
  <pre><code>+-----------+---------+------------+------------+
  | TripCount | bike_id |  Min_date  |  Max_date  |
  +-----------+---------+------------+------------+
  |         6 |     876 | 2014-05-22 | 2014-05-27 |
  |        18 |     323 | 2013-08-30 | 2013-09-07 |
  |        20 |     565 | 2013-08-29 | 2013-09-07 |
  |        24 |     476 | 2013-08-29 | 2013-09-07 |
  |        24 |     697 | 2013-10-15 | 2013-12-20 |
  +-----------+---------+------------+------------+
  </code></pre>
  
  <p>where the Min Date &amp; Max Date give respectively the first &amp; last date the bike made a trip.</p>
  
  <p>I can do this manually for each bike by running this:</p>
  
  <pre><code>SELECT 
  min(cast([start_date] as date)) as Min_Date
  ,max(cast([start_date] as date)) as Max_Date
  ,[bike_id]
  FROM [dbo].[trip]
  where bike_id = '697'
  Group By bike_id
  </code></pre>
  
  <p>I tried this query:</p>
  
  <pre><code>/****** Script for SelectTopNRows command from SSMS  ******/
  SELECT 
  count (t.[id]) as TripCount
  ,min(cast(t.[start_date] as date)) as Min_Date
  ,max(cast(t.[start_date] as date)) as Max_Date
  ,[bike_id]
  FROM [dbo].[trip] t
  Group By count (t.[id]), bike_id
  </code></pre>
  
  <p>and obviously got the following error:
  <em>Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.</em></p>
  
  <p>Just not quite sure how to wrap my head around this...</p>