Python Sqlalchemy counting duplicate results in query

Question

I have a query

allcategory =  db.session.query(func.count(marketItem.subcategory).label("help"),Subcategories.subname1)
allcategory = allcategory.join(Subcategories)
allcategory = allcategory.filter(marketItem.subcategory == Subcategories.id)
allcategory = allcategory.filter(marketItem.vendor_id==vendorprofile.id)
allcategory = allcategory.group_by(marketItem.id)
for allcat in allcategory.all():
    print(allcat)

thats outputs the results as

(1, 'Consoles')
(1, 'Consoles')
(1, 'Paintings')
(1, 'Paintings')
(1, 'Paintings')

I would like them to be

(2, 'Consoles')
(3, 'Paintings')

If it could also be explained in regular sql terms, that would help as well.

mechanical_meat · Accepted Answer · 2017-05-17 20:41:05Z

3

You should be grouping by Subcategory.subname1:

allcategory =  db.session.query(func.count(marketItem.subcategory).label("help"),
                                Subcategories.subname1)
allcategory = allcategory.join(Subcategories)
allcategory = allcategory.filter(marketItem.subcategory==Subcategories.id)
allcategory = allcategory.filter(marketItem.vendor_id==vendorprofile.id)
allcategory = allcategory.group_by(Sucategories.subname1) # < change here
for allcat in allcategory.all():
    print(allcat)

In SQL you must group by the fields that are not being aggregated -- in this case Subcategories.subname1.

answered May 17, 2017 at 20:41

mechanical_meat

170k25 gold badges238 silver badges231 bronze badges

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

Collectives™ on Stack Overflow

Python Sqlalchemy counting duplicate results in query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related