给定以下orm类：

class Entry(Base):
    __tablename__ = ...

    ID = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
    TYPE = sqlalchemy.Column(sqlalchemy.String, nullable=False)
    ITEM = sqlalchemy.Column(sqlalchemy.String, nullable=False)

您可以通过以下查询实现这一点：

COUNT = sqlalchemy.func.count(Entry.ITEM).label("COUNT")

query = session.query(Entry.ITEM, COUNT)   # SELECT ITEM, count(ITEM) as COUNT FROM TableName
query = query.filter(Entry.TYPE=='food')   # WHERE Type='food'
query = query.group_by(Entry.ITEM)         # GROUP BY ITEM
query = query.order_by(COUNT.desc())       # ORDER BY COUNT DESC

这个 query 然后可以在以下对象上迭代对象：

for item, amount in query:
    print(f"{item}: {amount}")

下面是一个完整的工作示例：

import os
import sqlalchemy, sqlalchemy.ext.declarative, sqlalchemy.orm

SQLITE_FILE = "example.sqlite"

if os.path.exists(SQLITE_FILE):
    os.remove(SQLITE_FILE)

engine = sqlalchemy.create_engine(f"sqlite:///{SQLITE_FILE}")

Base = sqlalchemy.ext.declarative.declarative_base(bind=engine)

class Entry(Base):
    __tablename__ = "entries"

    ID = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
    TYPE = sqlalchemy.Column(sqlalchemy.String, nullable=False)
    ITEM = sqlalchemy.Column(sqlalchemy.String, nullable=False)

Base.metadata.create_all()

create_session = sqlalchemy.orm.session.sessionmaker(bind=engine)
session:sqlalchemy.orm.Session = create_session()

example_types = (['food']*6) + (['drink']*2) + (['food']*2)
example_items = [
    'apple',
    'apple',
    'orange',
    'apple',
    'banana',
    'banana',
    'cola',
    'water',
    'banana',
    'apple'
]

example_entries = [
    Entry(
        TYPE=example_type, 
        ITEM=example_item
    ) 

    for example_type, example_item in zip(
        example_types, 
        example_items
    )
]

for example in example_entries:
    session.add(example)

session.commit()

# https://stackoverflow.com/questions/22479759

"""
select Item, count(Item) as COUNT
FROM TableName
WHERE Type='food'
GROUP BY Item
ORDER BY COUNT DESC
"""

COUNT = sqlalchemy.func.count(Entry.ITEM).label("COUNT")

query = session.query(Entry.ITEM, COUNT)   # select ITEM, count(ITEM) as COUNT FROM TableName
query = query.filter(Entry.TYPE=='food')   # WHERE Type='food'
query = query.group_by(Entry.ITEM)         # GROUP BY ITEM
query = query.order_by(COUNT.desc())       # ORDER BY COUNT DESC

for item, amount in query:
    print(f"{item}: {amount}")