Oh, I think I see what you are getting at .. basically you are getting duplicate item_sids because they represent different views.
... try this: select item_sid, ip_number, session_id, count(*) from item_raw group by item_sid, ip_number, session_id; On Mon, Feb 21, 2011 at 11:54 AM, Cam Bazz <camb...@gmail.com> wrote: > Hello, > > I did not understand this: > > when I do a: > > select item_sid, count(*) from item_raw group by item_sid > > i get hits per item. > > how do we join this to the master table? > > best regards, > -c.b. > > On Mon, Feb 21, 2011 at 6:28 PM, Ajo Fod <ajo....@gmail.com> wrote: > > You can group by item_sid (drop session_id and ip_number from group by > > clause) and then join with the parent table to get session_id and > > ip_number. > > > > -Ajo > > > > On Mon, Feb 21, 2011 at 3:07 AM, Cam Bazz <camb...@gmail.com> wrote: > >> > >> 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. > > > > >
