CODEWITHSUNDEEP
sqlselectcount
user3727752
user3727752

Reputation: 13

Need a Count, but Multiple other fields

I have a table that looks like this:

person    trip_id    date    home      destination
joe       1          3/10    chicago   new york
joe       2          4/10    chicago   l.a.
joe       3          5/10    chicago   boston
luther    4          3/12    new york  chicago
luther    5          3/18    new york  boston

I want to get a result like

person    trips    firstDate   home
joe       3        3/10        chicago
luther    2        3/12        new york

Currently I've got Select person, count(trip_id) as trips, min(date) as firstDate from [table] group by person order by firstDate

I can't figure out how to get home in there as well.

Home is always unique to the person. But my DBMS doesn't know that.

Is there an easy way around this problem?

Appreciate it.

Upvotes: 1

Views: 31

Answers (1)

Jaaz Cole
Jaaz Cole

Reputation: 3180

Select person, count(trip_id) as trips, min(date) as firstDate, home
from [table]
group by person, home
order by firstDate

Upvotes: 2

Related Questions