SQLAlchemy group by and count

SQL COUNT() with GROUP by

https://www.w3resource.com/sql/aggregate-functions/count-with-group-by.php

The use of COUNT() function in conjunction with GROUP BY is useful for characterizing our data under various groupings. A combination of same values (on a column) will be treated as an individual group.

sqlalchemy ORM

https://github.com/fanqingsong/sqlalchemy_demo/blob/master/answers/answer2.py

# Import the Declarative objects from the db module.
from db import Session, Artists, Albums
from db import engine
import pandas as pd
from sqlalchemy import func, desc

# Instantiate a new Session object which serves as the interface to the database.
sess = Session()

# Write a query that will display the 3 artists with the most albums as well as the number of albums.
# HINT: It will be necessary to aggregate results. Aggregation functions can be implemented using
# sqlalchemy.func. The documentation can be found here:
# http://http://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.expression.func
query = sess.query(
    Artists.name,
    func.count(Albums.title).label('number_of_albums')
).join(
    Albums
)\
.group_by(
    Artists.name
)\
.order_by(
    desc('number_of_albums')
)

"""The equivalant SQL for this query is:
"SELECT artists.name, count(albums.title) AS number_of_albums
FROM artists
    JOIN albums
        ON artists.artistid = albums.artistid
GROUP BY artists.name
ORDER BY number_of_albums DESC"
The group_by and order_by functions work very similar to the equivalent SQL clauses. The label function is used to to
specify a name for the aggregated count column, otherwise, SQLAlchemy would use the default name of "count_1."
"""

# Print the results.
example2 = pd.read_sql_query(query.statement, engine)
print(example2)
# print(example2[:3])

API

https://docs.sqlalchemy.org/en/14/orm/query.html#sqlalchemy.orm.Query.count

from sqlalchemy import func

# count User records, without
# using a subquery.
session.query(func.count(User.id))

# return count of user "id" grouped
# by "name"
session.query(func.count(User.id)).\
        group_by(User.name)

from sqlalchemy import distinct

# count distinct "name" values
session.query(func.count(distinct(User.name)))