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. > > > > >
