How to do groupby count

[John Ho]

In SQL I can do:

SELECT DISTINCT status, COUNT(*) FROM person GROUP BY status;

db().select(db.person.status, db.person.status.count(), distinct=True, groupby=db.person.status)

I get, BEAUTIFY:

Active <_extra> 99 Inactive <_extra> 1

How do I get rid of the <_extra>?

[jonatha...@whatho.net]

These days I don't write code, I get Claude to do it, but just to see,  I copied your question into ChatGPT and got this response:

In pyDAL, alias the aggregate expression to give it a proper name instead of having it appear under <_extra>.

count_expr = db.person.status.count().with_alias("total")

rows = db().select(
    db.person.status,
    count_expr,
    groupby=db.person.status
)
You can then access the values as:

for row in rows:
    print(row.person.status, row.total)
distinct=True is not needed here, since GROUP BY already produces one row per distinct status.

The <_extra> output is pyDAL’s way of representing an unnamed calculated expression. Giving the COUNT() expression an alias makes the result much cleaner.

Looks good to me. Not tested though.

Jonathan

[John Ho]

Thanks. Jonathan,

I was looking for SQL alias keyword "AS", the documentation is missing the index, so it is hard to search for py4web's implementation.  I admit I didn't read the documentation start to end.  I just read the sections of interest.

I think your example should be included in the documentation, any use of the count() will need to alias the results.

Anyway, I simply wrote it as SQL and executesql().  I am still debating whether to implement all my queries as SQL, although I like some aspect of py4web.