Home » SQL & PL/SQL » SQL & PL/SQL » Using WHEN COUNT function in conjunction with Partition By
Using WHEN COUNT function in conjunction with Partition By [message #397161] Thu, 09 April 2009 04:41 Go to next message
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

Re: Using WHEN COUNT function in conjunction with Partition By [message #397162 is a reply to message #397161] Thu, 09 April 2009 04:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Using WHEN COUNT function in conjunction with Partition By [message #397163 is a reply to message #397162] Thu, 09 April 2009 04:52 Go to previous messageGo to next message
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
Re: Using WHEN COUNT function in conjunction with Partition By [message #397169 is a reply to message #397163] Thu, 09 April 2009 05:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Using WHEN COUNT function in conjunction with Partition By [message #397171 is a reply to message #397169] Thu, 09 April 2009 05:14 Go to previous messageGo to next message
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
Re: Using WHEN COUNT function in conjunction with Partition By [message #397182 is a reply to message #397171] Thu, 09 April 2009 05:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Using WHEN COUNT function in conjunction with Partition By [message #397191 is a reply to message #397182] Thu, 09 April 2009 06:08 Go to previous messageGo to next message
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.
Re: Using WHEN COUNT function in conjunction with Partition By [message #397195 is a reply to message #397191] Thu, 09 April 2009 06:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select 1
  2  from emp
  3  having count(*) > 1
  4  /
         1
----------
         1

1 row selected.

Regards
Michel
Re: Using WHEN COUNT function in conjunction with Partition By [message #397230 is a reply to message #397195] Thu, 09 April 2009 09:21 Go to previous messageGo to next message
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?
Re: Using WHEN COUNT function in conjunction with Partition By [message #397261 is a reply to message #397230] Thu, 09 April 2009 10:55 Go to previous message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
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

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:
  


Current Time: Thu Dec 08 04:08:29 CST 2016

Total time taken to generate the page: 0.09679 seconds