yes, ip_number and session_id should not be in group by clause. 2011/2/22 Viral Bajaria <[email protected]>
> 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 <[email protected]> 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 <[email protected]> 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 <[email protected]> >> >> >> >> 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. >> > >> > >> > >
