Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
adrianmuller
Starting Member
4 Posts |
 Posted - 2009-04-09 : 04:50:32
|
| This is my query so far: and I want to introduce the condition that I want results only for users that their click count=5, how can I introduce in my query the WHEN COUNT (CLICKS) > 5 condition. I dnt know how to introduce this function, because I can't use group by. SELECT COMPUTER_ID, CLICK_DATE_1, CLICK_DATE_2, ADV.DB_FUNC_DATEDIFF('DD', CLICK_DATE_1, CLICK_DATE_2), NUM FROM (SELECT COMPUTER_ID, TO_CHAR (CLICK_DATE, 'YYYYMMDD') CLICK_DATE_1, TO_CHAR(LEAD(CLICK_DATE) OVER (PARTITION BY COMPUTER_ID ORDER BY CLICK_DATE), 'YYYYMMDD') CLICK_DATE_2, RANK() OVER (PARTITION BY COMPUTER_ID ORDER BY CLICK_DATE)NUM FROM ADV.USER_CLICKS_SCORING_IT WHERE CONVERTIONS =0 AND CLICKS >0 AND INSTALLDT >= TO_DATE (''19/11/2008'', 'DD/MM/YYYY') )WHERE CLICK_DATE_2 IS NOT NULL |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-04-09 : 05:07:30
|
quote:
Originally posted by adrianmuller
This is my query so far: and I want to introduce the condition that I want results only for users that their click count=5, how can I introduce in my query the WHEN COUNT (CLICKS) > 5 condition. I dnt know how to introduce this function, because I can't use group by. SELECT COMPUTER_ID, CLICK_DATE_1, CLICK_DATE_2, ADV.DB_FUNC_DATEDIFF('DD', CLICK_DATE_1, CLICK_DATE_2), NUM FROM (SELECT COMPUTER_ID, TO_CHAR (CLICK_DATE, 'YYYYMMDD') CLICK_DATE_1, TO_CHAR(LEAD(CLICK_DATE) OVER (PARTITION BY COMPUTER_ID ORDER BY CLICK_DATE), 'YYYYMMDD') CLICK_DATE_2, RANK() OVER (PARTITION BY COMPUTER_ID ORDER BY CLICK_DATE)NUM FROM ADV.USER_CLICKS_SCORING_IT WHERE CONVERTIONS =0 AND CLICKS >0 AND INSTALLDT >= TO_DATE (''19/11/2008'', 'DD/MM/YYYY') )WHERE CLICK_DATE_2 IS NOT NULL
Post your question at Oracle forums as SQLTeam is specific to MS SQL ServerMadhivananFailing to plan is Planning to fail |
 |
|
|
|
|
|
|
|
Using WHEN COUNT function in conjunction with Part