multi tables count

[Patito Feo]

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

[Massimiliano]

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])

--
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+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/b1c42e0f-487b-4796-84ca-702298c399f8%40googlegroups.com.

--

Massimiliano

[Patito Feo]

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.

To unsubscribe from this group and stop receiving emails from it, send an email to web...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/b1c42e0f-487b-4796-84ca-702298c399f8%40googlegroups.com.

--

Massimiliano