64

Let's say I have a table 'shares' with the following columns:

company    price    quantity
Microsoft  100      10
Google     99       5
Google     99       20
Google     101      15

I'd like to run the equivalent of a SQL statement like this:

select price, 
       sum(quantity) as num 
from shares 
where company='Google' 
group by price;

The closest I've come is:

result = (dbsession.query(Shares.price, func.sum(Shares.quantity))
         .filter(Shares.company == 'Google')
         .group_by(Shares.price)
         .all())

I'm having trouble with setting up the 'sum(quantity) as num' in sqlalchemy. It appears I need to use alias() but I can't figure out how by looking at the documentation.

Braiam
  • 1
  • 11
  • 47
  • 78
Vishakh
  • 1,168
  • 1
  • 11
  • 20

1 Answers1

150

You actually want the label method.

result = dbsession.query(
    Shares.price,
    func.sum(Shares.quantity).label("Total sold")
) \
.filter(Shares.company== 'Google') \
.group_by(Shares.price).all()
André C. Andersen
  • 8,955
  • 3
  • 53
  • 79
Sean Vieira
  • 155,703
  • 32
  • 311
  • 293
  • @user1193814 - **Excellent**, glad I could help! Welcome to Stackoverflow by the by! (If the answer helped you, click the green checkbox to let others know it worked.) – Sean Vieira Feb 08 '12 at 03:56
  • 2
    can we use `.label` on column names as well? Like `query(Shares.type.label("shares_type")).group_by(shares_type)`? – exAres Aug 05 '14 at 09:10
  • 1
    Yes you can, but simply using a label doesn't make it available as a variable in Python so you'll need to group by the field: `query(Shares.type.label("shares_type")).group_by(Shares.type)` – Sean Vieira Aug 05 '14 at 13:54
  • 1
    @Sangram Yes, just put it in quotes. `.group_by("shares_type")`. Discussion [here](https://stackoverflow.com/a/15557759/1749551). – Nick K9 Mar 28 '19 at 22:15
  • hi i have question , how i to code this sql for sqlalchemy? ```sql select jsonb_array_elements(subcellular_location) as ttt FROM protein GROUP BY ttt``` – 夏溪辰 Dec 17 '21 at 07:30
  • 2
    @夏溪辰 - best to ask a separate question for that. – Sean Vieira Dec 18 '21 at 00:20
  • How do I do the same for booleans: select(obj1.col1 > 50) –  May 11 '22 at 20:59