Well, I just tried your reply and it works. I didnt know i was able to groupby mutiple fields and the count should be the id. One complete week stuck in this.
Thanks a lot. El martes, 15 de octubre de 2019, 15:52:25 (UTC-5), Massimiliano escribió: > > I don't understand exactly what you mean, but maybe, you want to count > itemspedidos.id grouping by [db.pedidos.fecha.month(), > db.itemspedidos.COD_REFE] > > rows=db( (db.pedidos.fecha.year() == '2019' )&( db.itemspedidos.COD_PEDI > == db.pedidos.id )).select( db.itemspedidos.COD_REFE, > db.itemspedidos.id.count(), db.pedidos.fecha.month(), > orderby=db.pedidos.fecha.month(), groupby=[db.pedidos.fecha.month(), > db.itemspedidos.COD_REFE]) > > On Tue, Oct 15, 2019 at 10:07 PM Patito Feo <patito...@gmail.com > <javascript:>> wrote: > >> Hi, >> >> Ive been trying many ways of getting this right, >> >> db.define_table('pedidos', >> Field('fecha', type='date' ), >> Field('cliente', ), >> Field('pares', ), >> Field('estado', ), >> Field('noitems', ), >> Field('notapedido', ), >> Field('tipopedido', ), >> Field('vendedor', ), >> ) >> >> db.define_table('itemspedidos', >> Field('NUM_ITEM', 'integer'), >> Field('COD_PEDI', 'integer'), >> Field('COD_CLIE', ), >> Field('OD_SUCU' ), >> Field('TIP_PEDI', ), >> Field('TIP_BASE', ), >> Field('COD_REFE', ), >> Field('COD_COLO', ), >> Field('CAN_PEDI', 'integer' ), >> Field('CAMPO_01', 'integer' ), >> Field('CAMPO_02', 'integer' ), >> Field('CAMPO_03', 'integer' ), >> Field('CAMPO_04', 'integer' ), >> Field('CAMPO_05', 'integer' ), >> Field('CAMPO_06', 'integer' ), >> Field('CAMPO_07', 'integer' ), >> Field('CAMPO_S1', 'integer' ), >> Field('CAMPO_S2', 'integer' ), >> Field('CAMPO_S3', 'integer' ), >> Field('CAMPO_S4', 'integer' ), >> Field('CAMPO_S5', 'integer'), >> Field('CAMPO_S6', 'integer'), >> Field('CAMPO_S7', 'integer' ), >> Field('CAN_DOCE', 'integer' ), >> ) >> >> I need to find pedidos by month using pedidos.fecha and to count how many >> itemspedidos.COD_REFE are per month by each itemspedidos.COD_REFE which is >> the item reference. >> >> rows=db( db.pedidos.fecha.year() == '2019' )( db.itemspedidos.COD_PEDI == >> db.pedidos.id ).select( db.itemspedidos.COD_REFE, >> db.itemspedidos.COD_REFE.count( distinct=True ), db.pedidos.fecha.month(), >> orderby=db.pedidos.fecha.month(), groupby=db.pedidos.fecha.month(), ) >> >> This is a sample of what i get: >> >> itemspedidos.COD_REFE EXTRACT(month FROM "pedidos"."fecha") >> 132 MILAN 4 >> 132 MILAN 4 >> 132 MILAN 4 >> 113 SARA 4 >> 122 CLAUDIA NIÑA 4 >> 122 CLAUDIA NIÑA 4 >> 122 CLAUDIA NIÑA 4 >> 122 CLAUDIA NIÑA 4 >> >> As you can see i get the month and the itemspedidos.COD_REFE, but im >> unable to count by itemspedidos.COD_REFE per month and per unique >> itemspedidos.COD_REFE. >> >> Some advice would be nice, >> >> >> Thanks in advance >> >> >> -- >> Resources: >> - http://web2py.com >> - http://web2py.com/book (Documentation) >> - http://github.com/web2py/web2py (Source code) >> - https://code.google.com/p/web2py/issues/list (Report Issues) >> --- >> 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 web...@googlegroups.com <javascript:>. >> To view this discussion on the web visit >> https://groups.google.com/d/msgid/web2py/b1c42e0f-487b-4796-84ca-702298c399f8%40googlegroups.com >> >> <https://groups.google.com/d/msgid/web2py/b1c42e0f-487b-4796-84ca-702298c399f8%40googlegroups.com?utm_medium=email&utm_source=footer> >> . >> > > > -- > Massimiliano > -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- 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. To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/02b803a2-317f-4d3c-bcd0-450337f9f169%40googlegroups.com.