PostgreSQL and groupby

[voltron]

PostgreSQL is very picky when using something like "groupby" How doe I
pass multiple values to the groupby attribute in web2pys ORM select
function?

I get this:

self._execute=lambda *a,**b: self._cursor.execute(*a,**b)
ProgrammingError: column "user_logs.id" must appear in the GROUP BY
clause or be used in an aggregate function


thanks

[voltron]

This works:

custom_count='count(book_title)'
.select(db.book_logs.book_title,custom_count,
groupby=db.book_logs.book_title)


This does not work for example:

custom_count='count(book_title, book_name)'
.select(db.book_logs.book_title, db.books.book_name, custom_count,
groupby=(db.book_logs.book_title, db.books.book_name))

Thanks

[Massimo Di Pierro]

You should be able to use the "|" operator

[voltron]

Can you please give me an example? I am confuesd because its not a
logical OR, its a Postgres thing


Thanks

[Massimo Di Pierro]

(a==1)|(b==2) is translated in "(a=1) OR (b=2)"

but

a|b is translated in "a, b"

so you can do orderby=db.table.field|db.table.otherfield

Massimo

[voltron]

Thanks!

[web2py_tn]

but that defeats the purpose if I order by all the fields.

I've been trying for hours now get the correct way of grouping by ( groupby table.field ) in postgresql.

Any help?

[Niphlod]

On Tuesday, September 3, 2013 1:57:15 AM UTC+2, web2py_tn wrote:

but that defeats the purpose if I order by all the fields.

I've been trying for hours now get the correct way of grouping by ( groupby table.field ) in postgresql.

Any help?

ehm... what ? orderby and groupby takes exactly the fields in the same way: separated by a | 

[Bassem Bouguerra]

but I want to orderby one field and not all the fields. I want to orderby Table1.id only.

--
 
---
You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/wWdowJGAs7I/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

--

http://bouguerra.org

[Niphlod]

you have a problem with what you want and what you get....

Assuming a table

id      name       surname
1       tywin      lannister
2       tyrion     lannister
3       eddard     stark
4       sansa      stark

and a query where you want to know how many members of the Game Of Thrones families you have, you'll likely want to issue a

select count(*), surname from table group by surname

the resultset will be

count(*)    surname
2           lannister
2           stark

now..... you can't even think to "order" by id, because there is no id at all included in your resultset ^_^

[Bassem Bouguerra]

I am sorry. I am trying to groupby and not orderby. 

I want to select from 2 tables and groupby the id of one table. This works fine in sqllight but throws an error on postgresql.

Massimo suggested that I add the id of the second table using "|". However, then I can't get the result I need, which is grouping by the id of the first table only.

I hope this makes sense.

--

 
---
You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/wWdowJGAs7I/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

--

http://bouguerra.org

[Niphlod]

please post your model, a simple data set and what you want as a resultset....

[Johann Spies]

On 3 September 2013 11:42, Bassem Bouguerra <boug...@gmail.com> wrote:

I am sorry. I am trying to groupby and not orderby. 

I want to select from 2 tables and groupby the id of one table. This works fine in sqllight but throws an error on postgresql.

You cannot do that in Postgresql.  If you want to group by in Postresql you have to use all the fields in the groupby part that was used in the 'select' part of the query.

Regards
Johann

[Derek]

maybe this way:

select min(id), count(*), surname from table group by surname

?

[Niphlod]

this doesn't return any other meaningful info about the id of the group, it just uses an aggregate on it. In that case, you'd still need to orderby by min(id).
tl;dr : any groupby query can be ordered either by a field included in a group by or by an aggregate of any other column.

[Michele Comitini]

Given:

SELECT t1.t1_f1, max(t2.t2_f1) FROM t1 JOIN t2 on t1.id = t2.t1_id GROUP BY t1.t1_f1 ORDER BY max(t2.t2_f1), t1.t1_f1;

If I recall correctly in PostgreSQL you can do:

SELECT t1.t1_f1, max(t2.t2_f1) FROM t1 JOIN t2 on t1.id = t2.t1_id GROUP BY 1 ORDER BY 2,1;

i.e. you can use the positional arguments in place of the expressions.

2013/9/3 Niphlod <nip...@gmail.com>

--
 
---
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+un...@googlegroups.com.

[Niphlod]

that syntax (orderby positional arguments) should work in any relation db, it's standard t-sql.