[web2py] Re: Count and distinct
In documentation i see: >>> count = db.person.id.count() >>> for row in db(db.person.id==db.thing.owner_id).select( db.person.name, count, groupby=db.person.name): print row.person.name, row[count] Alex 2 Bob 1 but i need to limit the number of rows -- --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: Count and distinct
what's the purpose of limiting the number of rows you have to count ? On Thursday, April 25, 2013 11:39:50 AM UTC+2, Domagoj Kovač wrote: > > In documentation i see: > > >>> count = db.person.id.count() > >>> for row in db(db.person.id==db.thing.owner_id).select( > db.person.name, count, groupby=db.person.name): > print row.person.name, row[count] > Alex 2 > Bob 1 > > > but i need to limit the number of rows > -- --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: Count and distinct
You misunderstood me. In the post above there is a regular select without limitby clause, i want to limit number of records in that select, so i assume count will be also limit? -- --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: Count and distinct
that's more or less exactly what I don't understand. you usually count() something without any limits because you don't know the cardinality of your wanted resultset. If you limit your resultset to n rows, what's the purpose of counting ? That's why there's no db(query).count(limitby=(0,n)) syntax On Thursday, April 25, 2013 2:12:16 PM UTC+2, Domagoj Kovač wrote: > > You misunderstood me. In the post above there is a regular select without > limitby clause, i want to limit number of records in that select, so i > assume count will be also limit? -- --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: Count and distinct
count = db.person.id.count() I understand this line of code, it is obvious that limitby clause here would be stupid. >>> for row in db(db.person.id==db.thing.owner_id).select( db.person.name, count, groupby=db.person.name): print row.person.name, row[count] Alex 2 Bob 1 I don't understand what is the purpose of the count here, and is this count related to the count above. -- --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: Count and distinct
Yeah, I agree. I read the documentation and I have no clue what is happening there. Are there two people named Alex? Does 'Alex' own two items? Why is it grouping by person name instead of person id? Why is 'count' assigned as a count of person IDs, used as an argument in the select (presumably it's just an int?) and then used as an iterator (assuming the count of person IDs is 2, then it's just getting row 2?)? On Thursday, April 25, 2013 12:55:46 PM UTC-7, Domagoj Kovač wrote: > > count = db.person.id.count() > > I understand this line of code, it is obvious that limitby clause here > would be stupid. > > >>> for row in db(db.person.id==db.thing.owner_id).select( > db.person.name, count, groupby=db.person.name): > print row.person.name, row[count] > Alex 2 > Bob 1 > > I don't understand what is the purpose of the count here, and is this > count related to the count above. > > > -- --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: Count and distinct
db.person.id.count() returns an Expression object. It generates SQL like "COUNT(person.id)". When it is passed to the .select(), the count is returned as one of the columns in the result. The key used to identify the count column in each Row object of the result is the count SQL itself (i.e., "COUNT(person.id)"), so you can access the count in each row via: row['COUNT(person.id)'] However, the __str__ method of the Expression object will also return the SQL, so you can instead do: row[str(count)] To make things even easier, if you pass an Expression object as the key to a Row, it will automatically apply the __str__ method, so you can just do: row[count] which is the method used in the book example. Anthony On Thursday, April 25, 2013 3:55:46 PM UTC-4, Domagoj Kovač wrote: > > count = db.person.id.count() > > I understand this line of code, it is obvious that limitby clause here > would be stupid. > > >>> for row in db(db.person.id==db.thing.owner_id).select( > db.person.name, count, groupby=db.person.name): > print row.person.name, row[count] > Alex 2 > Bob 1 > > I don't understand what is the purpose of the count here, and is this > count related to the count above. > > > -- --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: Count and distinct
I understand now, there are two pepople named Alex and one named Bob. Much better example would be to put example like: >>> for row in db(db.person.id==db.thing.owner_id).select( db.person.name, count, groupby=db.person.name): print row.person.name, row[count] Alex 2 Bob 4 Here is much more obvious what is going on, because result revels that count is actually number of the people with the name in that row. -- --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: Count and distinct
Now this makes sense. The book does not. On Thursday, April 25, 2013 2:27:34 PM UTC-7, Anthony wrote: > > db.person.id.count() returns an Expression object. It generates SQL like > "COUNT(person.id)". When it is passed to the .select(), the count is > returned as one of the columns in the result. The key used to identify the > count column in each Row object of the result is the count SQL itself > (i.e., "COUNT(person.id)"), so you can access the count in each row via: > > row['COUNT(person.id)'] > > However, the __str__ method of the Expression object will also return the > SQL, so you can instead do: > > row[str(count)] > > To make things even easier, if you pass an Expression object as the key to > a Row, it will automatically apply the __str__ method, so you can just do: > > row[count] > > which is the method used in the book example. > > Anthony > > On Thursday, April 25, 2013 3:55:46 PM UTC-4, Domagoj Kovač wrote: >> >> count = db.person.id.count() >> >> I understand this line of code, it is obvious that limitby clause here >> would be stupid. >> >> >>> for row in db(db.person.id==db.thing.owner_id).select( >> db.person.name, count, groupby=db.person.name): >> print row.person.name, row[count] >> Alex 2 >> Bob 1 >> >> I don't understand what is the purpose of the count here, and is this >> count related to the count above. >> >> >> -- --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
