Hey Massimo:
Let me try to explain it:
End goal:
Display the total amount of non-duplicated items inserted every date.
I have the logs table that has many items that are inserted at a given
date (item a, b, c, d, etc) . The problem is that the same item might
get inserted several times at the same date. But i just want to count
that as only one item.
The inner:
SELECT
logs.date, logs.items
FROM logs
WHERE
extract(year from logs.date) = 2010)
GROUP BY date, items
gets rid of the duplicated items, but still have several rows for the
same day (for unique items).
That's the reason I have the main select (besides applying some other
WHERE clauses that I took out for the sake of simplicity).
I could actually do this query as a single one by using
count(distinct(foo.items)) in the main select, but that's still not
supported by web2py (i'm following the other thread about that).
Hope I clarify the point Massimo, thanks for taking the time to look at this!
beto
On Sat, Jan 29, 2011 at 11:45 AM, Massimo Di Pierro
<massimo.dipie...@gmail.com> wrote:
> Before we try write is? Can you explain it? There is a condition on
> stcok that seems a inner join but nothing is selected from the stock
> table.
>
>
>
>
>
> On Jan 28, 2:16 pm, "beto (R3)" <bet...@gmail.com> wrote:
>> Hey guys:
>>
>> Is there a way to do this query in DAL?
>>
>> SELECT
>> date, count(foo.items)
>> FROM
>> (
>> SELECT
>> logs.date, logs.items
>> FROM logs
>> WHERE
>> extract(year from logs.date) = 2010)
>> GROUP BY date, items
>> ) AS foo, stock
>> WHERE
>> stock.items = foo.items
>> GROUP by date
>>
>> Any help would be appreciated.
>>
>> thanks!
