Re: [web2py] multi tables count

2019-10-15 Thread 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.



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

Re: [web2py] multi tables count

2019-10-15 Thread 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])

On Tue, Oct 15, 2019 at 10:07 PM Patito Feo  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 web2py+unsubscr...@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

-- 
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/CANPTPxLbsgRpR6YG0r-Ne2ehbAJC5bU%2BJt1adNRW3UmiVgaeTg%40mail.gmail.com.

[web2py] multi tables count

2019-10-15 Thread 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


-- 
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/b1c42e0f-487b-4796-84ca-702298c399f8%40googlegroups.com.