In trunk: >>> db()._select(db.t.a.count(db.t.a)) 'SELECT COUNT(DISTINCT t.a) FROM t;' >>> db()._select(db.t.a.count()) 'SELECT COUNT() FROM t;' >>> db()._select(db.t.a.count(True)) 'SELECT COUNT(DISTINCT t.a) FROM t;'
Thank nihlod. On Friday, 13 July 2012 15:09:01 UTC-5, Niphlod wrote: > > ooookey, I finally got it (I guess). Men, sometimes reading through lines > of posts without examples is hard! > > @all: we're circling around here because no distinctions were properly > made in this thread for count() as a method attached to the Field vs the > count() method attached to the table. > > @Fabiano (and everyone else reading this and wanting help on "advanced > queries"): one example with a little data is worth more than a thousand > word. > > @all: time for examples. > tests.id > <http://127.0.0.1:8000/tests/appadmin/select/db?orderby=tests.id> > tests.id_impressora<http://127.0.0.1:8000/tests/appadmin/select/db?orderby=tests.id_impressora> > tests.data<http://127.0.0.1:8000/tests/appadmin/select/db?orderby=tests.data> > 1 <http://127.0.0.1:8000/tests/appadmin/update/db/tests/1> abcd > 2012-07-132 <http://127.0.0.1:8000/tests/appadmin/update/db/tests/2> > abcd2012-07-133 <http://127.0.0.1:8000/tests/appadmin/update/db/tests/3> > fghi2012-07-134 <http://127.0.0.1:8000/tests/appadmin/update/db/tests/4> > lmno2012-07-135 <http://127.0.0.1:8000/tests/appadmin/update/db/tests/5> > lmno2012-07-136 <http://127.0.0.1:8000/tests/appadmin/update/db/tests/6> > abcd2012-07-12 > Now, what I think Fabiano wants is: > 2012-07-12 1 > 2012-07-13 3 > i.e. How many distinct id_impressora values are in the table, grouped by > data ? > > Current DAL doesn't allow this. > You can count how many rows there are for every data. > db(db.tests.id>0).select(db.tests.data, db.tests.id_impressora.count(), > groupby=db.tests.data) > 2012-07-12 1 > 2012-07-13 5 > > You can count how many distinct records of id_impressora are in the table. > db(db.tests.id>0).count(db.tests.id_impressora) > 3 > > And that's pretty much all about it. > @Massimo: > now: db(db.tests.id>0)._count(db.tests.id_impressora) > SELECT count(DISTINCT id_impressora) FROM tests WHERE (tests.id > > 0); > a) add a groupby parameter to the count() method on the table. > db(db.tests.id>0)._count('id_impressora', groupby=db.tests.data) > SELECT count(DISTINCT id_impressora), tests.data FROM tests WHERE > (tests.id > 0) GROUP BY tests.data > however, to be of some significance when grouping by something the > field itself should be included into the select > b) add a distinct parameter to the count() method on the field > > db(db.tests.id>0)._select(db.tests.id_impressora.count(distinct=True), > db.tests.data, groupby=db.tests.data) > SELECT count(DISTINCT id_impressora), tests.data FROM tests WHERE > (tests.id >0) GROUP BY tests.data > > I vote for b. > >
