Sqlalchemy sum of true

Question

I have the follwoing table and want to compute the sum of true and group_by by date

| Date       | Value1      | Value2       |
|:-----------|------------:|:------------:|
| 2019-12-05 |        5.5  |     true|
| 2019-12-05 |        4.5  |     true|
| 2019-12-05 |        6.5  |     false|
| 2019-12-05 |        8.5  |     false|
| 2019-12-05 |        2.5  |     true|
| 2019-12-06 |        3.5  |     true|
| 2019-12-06 |        5.5  |     true|
| 2019-12-06 |        56.5 |    true|
| 2019-12-06 |        8.5  |     true|
| 2019-12-06 |        99.5 |     false|
| ...        |        ...  |     ...      |

my query on a db.Model by a classmethod:

return db.session.query(cls.Date, func.count(cast(cls.Value2, sqlalchemy.Integer)).filter(cls.Value2== True).label("Count True Value2")) \
    .group_by(cls.Date)\
    .order_by("Date")

Result should be

   | Date       | Value2     |
    |:-----------|------------:|
    | 2019-12-05 |        3  |     
    | 2019-12-06 |        4  |

EDIT does not work either

  return db.session.query(cls.Date, func.sum(case([(cls.Value2== True, 1)], else_=0).label('Value2'))) \
            .filter(cls.Date== current_date.strftime("%Y-%m-%d")) \
            .group_by(cls.Date)\
            .order_by("Date")

you can refer https://stackoverflow.com/a/5276024/1079086. – Akash senta Feb 03 '20 at 16:14 — Akash senta, Feb 03 '20 at 16:14

Danila Ganchar · Accepted Answer · 2020-02-04T08:00:36.590

You need to add Value2 to group condition.

Here is an example:

from datetime import date

from sqlalchemy import Integer
from sqlalchemy import func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy import Column, Date, Boolean
from sqlalchemy.orm import sessionmaker

Base = declarative_base()
engine = create_engine('...creds...', echo=True)


class TestModel(Base):
    __tablename__ = 'test_model'
    id = Column(Integer, primary_key=True)
    date = Column(Date)
    value2 = Column(Boolean)


Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
# a few test records
for date_ in (date(2020, 1, 1), date(2020, 1, 2)):
    session.add(TestModel(date=date_, value2=True))
    session.add(TestModel(date=date_, value2=True))
    session.add(TestModel(date=date_, value2=True))
    session.add(TestModel(date=date_, value2=False))

session.commit()

query = (
    session.query(
        TestModel.date,
        TestModel.value2,
        func.count('*').label('counter')
    )
    # uncomment if you need only True values
    # .filter(TestModel.value2.is_(True))
    .group_by(TestModel.date, TestModel.value2)
    .order_by(TestModel.date)
)

for rec in query:
    print('date {date}, value2 {value2}, counter {counter}'.format(
        date=rec.date,
        value2=rec.value2,
        counter=rec.counter,
    ))

# date 2020-01-01, value2 True, counter 3
# date 2020-01-01, value2 False, counter 1
# date 2020-01-02, value2 False, counter 1
# date 2020-01-02, value2 True, counter 3

Hope this helps.

@IljaEverilä this is not related to `sqlalchemy`. I can't remember the database or a specific case. I just remember some nuance with `BOOLEAN`, `NULL` or transformation. Maybe it was related with some database options. Do not remember seriously. — Danila Ganchar, Feb 03 '20 at 18:57
Maybe you've run into https://stackoverflow.com/questions/9822154/standard-sql-boolean-operator-is-vs-equals-operator. The three-valued logic in SQL is sometimes a bit surprising. — Ilja Everilä, Feb 04 '20 at 07:18
@IljaEverilä just became interesting and decided to remember... I think you are right. I thought about `nullable(boolean)` (`boolean` column without default value). `WHERE value2 != TRUE` and `WHERE value2 IS NOT TRUE`. In any case, this doesn't apply to the current context. — Danila Ganchar, Feb 04 '20 at 08:00

Sqlalchemy sum of true

1 Answers1