SQLalchemy merge two queries from same table

Question

I need to query the total sales & guest for each Store for yesterday and the same day last year with the goal to be able to display in a table using jinja2. I currently have two queries with a Union...

    this_year = db.session.query(Sales.name,
                                   func.sum(Sales.sales).label('total_sales'),
                                   func.sum(Sales.guests).label('total_guests')
                                   ).filter(Sales.date >= start_day,
                                            Sales.date <= end_day
                                            ).group_by(Sales.name)


    last_year = db.session.query(Sales.name,
                                   func.sum(Sales.sales).label('total_sales'),
                                    func.sum(Sales.guests).label('total_guests')
                                   ).filter(Sales.date >= start_day_ly,
                                           Sales.date <= end_day_ly
                                            ).group_by(Sales.name)


    daily_table = this_year.union(last_year).all()

... but it gives me a list like so:

STORE 1, SALES, GUESTS
STORE 1, SALES_LY, GUESTS_LY
STORE 2, SALES, GUESTS
STORE 2, SALES_LY, GUESTS_LY
ETC,,

what i want is:

STORE 1, SALES, SALES_LY, GUESTS, GUESTS_LY
STORE 2, SALES, SALES_LY, GUESTS, GUESTS_LY

class Sales(db.Model):

    __tablename__ = 'Sales'

    id = db.Column(db.Integer, primary_key=True)
    date = db.Column(db.String(64))
    daypart = db.Column(db.String(64))
    name = db.Column(db.String(64))
    sales = db.Column(db.Integer)
    guests = db.Column(db.Integer)

data: |id|date|daypart|name|sales|guests| |-----|-----|-----|-----|-----|-----| |14896| 2021-11-22| Dinner| STORE 1| 11250.05| 288| |14897| 2021-11-22| Lunch| STORE 1| 9250.25| 157| |14898| 2021-11-22| Dinner| STORE 2| 5764.95| 169| |14899| 2021-11-22| Lunch| STORE 2| 5856.25| 168| |14900| 2021-11-22| Dinner| STORE 3| 9186.7| 320| |14901| 2021-11-22| Lunch| STORE 3| 7521.0| 175|

This data goes back several years. I looked at GROUP_BY, SUBQUERY, CASE, JOIN but have not found a solution that works.

alim91 · Accepted Answer · 2021-11-26 12:14:14Z

1

you have to use with_entities, and use and_

this_year = db.session.query(Sales)
            .filter(and_(Sales.date >= start_day, Sales.date <= end_day))
            .with_entities(
                Sales.name.label('name'),
                func.sum(Sales.sales).label('total_sales'),
                func.sum(Sales.guests).label('total_guests')
            )
            .group_by(Sales.name)

last_year = db.session.query(Sales)
            .filter(and_(Sales.date >= start_day_ly,Sales.date <= end_day_ly))
            .with_entities(
                Sales.name.label('name'),
                func.sum(Sales.sales).label('total_sales'),
                func.sum(Sales.guests).label('total_guests')
            )
            .group_by(Sales.name)

daily_table = this_year.union(last_year).all()

answered Nov 26, 2021 at 12:14

alim91

5461 gold badge8 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

wandored Over a year ago

Sorry it took so long to respond. After posting, i found a work around by moving "all()" to this_year & that_year, making them a list and using pandas to merge the lists into a dataframe, which worked. I tried your code and it did exactly want i wanted in slightly less code. Which would be more efficient?

Collectives™ on Stack Overflow

SQLalchemy merge two queries from same table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related