yes. I do not know how to do it with one query.
On Mar 30, 3:18 pm, Joaquin Orbe <joaquino...@gmail.com> wrote: > On Sat, Mar 26, 2011 at 6:30 PM, Massimo Di Pierro > > > > > > > > > > <massimo.dipie...@gmail.com> wrote: > > 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. > > Hi Massimo, > sorry for my late answer. > > How should I applied your suggestion? As I can understand, your code > seems to have three row results, and in my case I need only one. Is it > correct my understanding? > > Thanks, > Joaquin.
