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.
