yes, this indeed solved my problem. thanks a bunch.
On Wed, Feb 23, 2011 at 4:33 AM, wd <w...@wdicc.com> wrote: > yes, ip_number and session_id should not be in group by clause. > > 2011/2/22 Viral Bajaria <viral.baja...@gmail.com> >> >> I am guessing the following query should work too: >> select item_sid, count(distinct ip_number, session_id) from item_raw where >> date_day = '20110202' group by item_sid; >> On Mon, Feb 21, 2011 at 9:42 PM, Cam Bazz <camb...@gmail.com> wrote: >>> >>> The query you have produced mulltiple item_sid's. >>> >>> This is rather what I have done: >>> >>> select u.item_sid, count(*) cc from (select distinct item_sid, >>> ip_number, session_id from item_raw where date_day='20110202') u group >>> by u.eser_sid >>> >>> date_day is a partition >>> >>> and this produced the results i wanted, but as you can see it is a >>> double query. I dont know if there is a single query way of doing it. >>> >>> best regards. >>> -c.b. >>> >>> On Tue, Feb 22, 2011 at 4:32 AM, wd <w...@wdicc.com> wrote: >>> > May be >>> > select item_sid, count(distinct ip_number, session_id) from item_raw >>> > group >>> > by item_sid, ip_number, session_id (I've not test it, maybe it should >>> > be >>> > concat(ip_number, session_id) instead of ip_number, session_id ) >>> > is what you want. >>> > >>> > 2011/2/21 Cam Bazz <camb...@gmail.com> >>> >> >>> >> Hello, >>> >> >>> >> So I have table of item views with item_sid, ip_number, session_id >>> >> >>> >> I know it will not be that exact, but I want to get unique views per >>> >> item, and i will accept ip_number, session_id tuple as an unique view. >>> >> >>> >> when I want to query just item hits I say: select item_sid, count(*) >>> >> from item_raw group by item_sid; >>> >> >>> >> but if I say: >>> >> >>> >> select item_sid, count(*) from item_raw group by item_sid, ip_number, >>> >> session_id; >>> >> >>> >> it will give me duplicate item sids. >>> >> >>> >> how can I query per unique tuple of ip_number, session_id per >>> >> item_sid? >>> >> >>> >> best regards, >>> >> c.b. >>> > >>> > >> > >
