CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
kiltannen
kiltannen

Reputation: 1137

Group By with Min and Max Date using T-SQL

I am trying to do some SQL on the data from the SF Bay Area Bike Share Kaggel Data

When I run this SQL:

/****** Select Bikes   ******/
SELECT count(t.[id]) as TripCount
,t.[bike_id]
FROM [dbo].[trip] t
Group By t.[bike_id]
having count(t.[id]) < 25
Order By TripCount asc

I get this result:

+-----------+---------+
| TripCount | bike_id |
+-----------+---------+
|         6 |     876 |
|        18 |     323 |
|        20 |     565 |
|        24 |     476 |
|        24 |     697 |
+-----------+---------+

Where what I really want is this:

+-----------+---------+------------+------------+
| 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 |
+-----------+---------+------------+------------+

where the Min Date & Max Date give respectively the first & last date the bike made a trip.

I can do this manually for each bike by running this:

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

I tried this query:

/****** 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

and obviously got the following error: Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

Just not quite sure how to wrap my head around this...

Upvotes: 3

Views: 5774

Answers (1)

Mihai
Mihai

Reputation: 26784

You were close

SELECT
[bike_id] 
,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
FROM [dbo].[trip] t
Group By  bike_id

Upvotes: 3

Related Questions