CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemy
Anekdotin
Anekdotin

Reputation: 1591

Python Sqlalchemy counting duplicate results in query

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.

Upvotes: 2

Views: 2207

Answers (1)

mechanical_meat
mechanical_meat

Reputation: 169544

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.

Upvotes: 3

Related Questions