Reputation: 1591
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
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