Using SQLAlchemy, how can I return a count with multiple columns
- by Andy
I am attempting to run a query like this:
SELECT
comment_type_id, name, count(comment_type_id)
FROM
comments, commenttypes
WHERE
comment_type_id=commenttypes.id
GROUP BY
comment_type_id
Without the join between comments and commenttypes for the name column, I can do this using:
session.query(Comment.comment_type_id,func.count(Comment.comment_type_id)).group_by(Comment.comment_type_id).all()
However, if I try to do something like this, I get incorrect results:
session.query(Comment.comment_type_id, Comment.comment_type, func.count(Comment.comment_type_id)).group_by(Comment.comment_type_id).all()
I have two problems with the results:
(1, False, 82920)
(2, False, 588)
(3, False, 4278)
(4, False, 104370)
Problems:
The False is not correct
The counts are wrong
My expected results are:
(1, 'Comment Type 1', 13820)
(2, 'Comment Type 2', 98)
(3, 'Comment Type 2', 713)
(4, 'Comment Type 2', 17395)
How can I adjust my command to pull the correct name value and the correct count?
