-1

I'm trying to do a group by + count with a weather stations table to answer the following questions:

What are the most active stations? (i.e. what stations have the most rows)?

List the stations and the counts in descending order

The table consists of the following values: id INTEGER station TEXT date TEXT prcp FLOAT tobs FLOAT

This is my current formulation of the select statement:

sel = [Measurement.station, func.count(Measurement.id)]
count = session.query(*sel).all()

count
ramango
  • 21
  • 1
  • 5

1 Answers1

1

As you mention, a GROUP BY clause needs to be applied to that query. This can be easily done in sqlalchemy: count = session.query(*sel).group_by(Measurement.station).all().

mathiascg
  • 550
  • 1
  • 5
  • 15
  • Thanks! Do you know how I would add an order by desc id count? – ramango Apr 10 '20 at 15:34
  • If you mean ordering by the station, you just need to add ```.order_by(Measurement.station.desc())``` before ```.all()```. You are aggregating by id, so you can't order by this column. – mathiascg Apr 10 '20 at 20:33