You an have a nested select in place of a field. I think you want: count = db.sales_order.id.count() query = db.sales_order.VendorID == db.vendor.id rows1 = db(query) (db.sales_order.Status==1).select(db.vendor.id,db.vendor.Name,count) rows2 = db(query) (db.sales_order.Status==2).select(db.vendor.id,db.vendor.Name,count) rows3 = db(query) (db.sales_order.Status==3).select(db.vendor.id,db.vendor.Name,count)
On Mar 26, 3:57 pm, Joaquin Orbe <joaquino...@gmail.com> wrote: > Hi all, > > I'm trying to get a result for this query: > > select vendor.id, vendor.Name, > (select count(*) from sales_order where [Status] = 1 and vendor.id = > sales_order.VendorID), > (select count(*) from sales_order where [Status] = 2 and vendor.id = > sales_order.VendorID), > (select count(*) from sales_order where [Status] = 3 and vendor.id = > sales_order.VendorID) > from vendor > > and for it I do: > > countOpen = db((db.sales_order.Status==1)&(db.sales_order.VendorID == > db.vendor.id)).count() > countProg = db((db.sales_order.Status==2)&(db.sales_order.VendorID == > db.vendor.id)).count() > countClosed = db((db.sales_order.Status==3)&(db.sales_order.VendorID > == db.vendor.id)).count() > > rows = db().select(db.vendor.id, > db.vendor.Name,countOpen,countProg,countClosed) > > but it's not working because I get > > (1, vendor1, 1, 4, 1) > (2, vendor2, 1, 4, 1) > > instead of > > (1, vendor1, 1, 4, 1) > (2, vendor2, 0, 0, 0) > > It seems the result of vendor1 is also applied to vendor2 (vendor2 > does not have records in sales_order table). > > Does someone know how to solve it? > > Thanks in advance, > Joaquin.
