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.
