OraFAQ Forum: SQL & PL/SQL » Using WHEN COUNT function in conjunction with Partition By

Home » SQL & PL/SQL » SQL & PL/SQL » Using WHEN COUNT function in conjunction with Partition By

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Using WHEN COUNT function in conjunction with Partition By [message #397161]

Thu, 09 April 2009 04:41

amuller
Messages: 3
Registered: April 2009

Junior Member

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. Or any workaround to get only the info for those users with more than 5 clicks. Thanks

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

Report message to a moderator

Re: Using WHEN COUNT function in conjunction with Partition By [message #397162 is a reply to message #397161]

Thu, 09 April 2009 04:47

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

HAVING COUNT (CLICKS) > 5

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel

Report message to a moderator

Re: Using WHEN COUNT function in conjunction with Partition By [message #397163 is a reply to message #397162]

Thu, 09 April 2009 04:52

amuller
Messages: 3
Registered: April 2009

Junior Member

But the having function requires me to use a group by statement, where exactly should i put it. thanks

Report message to a moderator

Re: Using WHEN COUNT function in conjunction with Partition By [message #397169 is a reply to message #397163]

Thu, 09 April 2009 05:11

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

amuller wrote on Thu, 09 April 2009 11:52

But the having function requires me to use a group by statement, where exactly should i put it. thanks

Your assumption is wrong.
Just try it.

Regards
Michel

Report message to a moderator

Re: Using WHEN COUNT function in conjunction with Partition By [message #397171 is a reply to message #397169]

Thu, 09 April 2009 05:14

amuller
Messages: 3
Registered: April 2009

Junior Member

Dear Michael,

Could you please tell me where I have to insert the having function. thanks in advanced for all the help

Report message to a moderator

Re: Using WHEN COUNT function in conjunction with Partition By [message #397182 is a reply to message #397171]

Thu, 09 April 2009 05:43

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please refer to SQL Reference before posting a question about syntax.
Where do you use to put the having clause?

Regards
Michel

Report message to a moderator

Re: Using WHEN COUNT function in conjunction with Partition By [message #397191 is a reply to message #397182]

Thu, 09 April 2009 06:08

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

I have to say that I'm with @amuller on this one.

I think you need a GROUP BY clause to use HAVING too, although I'm entirely willing to be proved wrong.

Report message to a moderator

Re: Using WHEN COUNT function in conjunction with Partition By [message #397195 is a reply to message #397191]

Thu, 09 April 2009 06:41

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SQL> select 1
  2  from emp
  3  having count(*) > 1
  4  /
         1
----------
         1

1 row selected.

Regards
Michel

Report message to a moderator

Re: Using WHEN COUNT function in conjunction with Partition By [message #397230 is a reply to message #397195]

Thu, 09 April 2009 09:21

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

Well, well, well.

That's today's learning experience taken care of.

Is that documented anywhere?

Report message to a moderator

Re: Using WHEN COUNT function in conjunction with Partition By [message #397261 is a reply to message #397230]

Thu, 09 April 2009 10:55

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Yes, SELECT syntax diagram shows that GROUP BY and HAVING can be used without the other one.
But, in fact, HAVING can be used without GROUP BY if there is only one group in the same way that SUM/COUNT/AVG... without GROUP BY.

Regards
Michel

Report message to a moderator

Previous Topic:	A question about SUM analytic function used with GROUP BY clause
Next Topic:	how to know sequence name on a table?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jul 12 14:18:50 CDT 2025