count(1) < 10

[FALCONSEYE]

What is the equivalent of the following t-sql statement?

select count(1)
from myTable
where count(1) < 10

http://www.fuzzysiberians.com

 

[FALCONSEYE]

I guess I will have to do this:


having count(1) < 10

http://www.fuzzysiberians.com

 

[Turkbear]

Hi,
What is count(1) ?

If it is a numeric field ( with an odd name) then your first statement should work fine...






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

Falcon,

What are you actually trying to do with that code? Even with the syntax change ("HAVING") that allows the code to work, in its current form, the code doesn't return anything:

select count(1) 
from myTable
where count(1) < 10;

no rows selected

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[SantaMufasa]

Sorry...I copied and pasted the wrong code. (Drat Tek-Tips for not yet allowing us to modify our own posting to fix goofs.) My code, above, should have read:

select count(1)
from s_emp
[B][I]HAVING[/I][/B] count(1) < 10
/

no rows selected

My earlier post (and Turkbear's assertion that "your first statement should work fine...") are incorrect since you cannot use WHERE to evaluate a group function (e.g., "COUNT")...That is what HAVING is for.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Turkbear]

Hi,
Thanks Santa,
I missed the ( now obvious ) fact that that was a Count() function, not a field from the database
I am too used to Count(*), in that situation...






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

Turkbear,

I thought that's exactly what happened...I just didn't want the momentary confusion to leave an impression with Falcon'sEye that we would need to address later.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[ChrisHunt]

in its current form, the code doesn't return anything

Surely that depends on the number of rows in [tt]myTable[/tt]. If it has less than ten rows, you get a result; if it doesn't, you don't:

SQL> SELECT COUNT(1)
  2  FROM dual
  3  HAVING COUNT(1) < 10
  4  /

  COUNT(1)
----------
         1


SQL> SELECT COUNT(1)
  2  FROM all_objects
  3  HAVING COUNT(1) < 10
  4  /

no rows selected

So I suppose you could use a statement like this to determine whether the table has less than 10 rows - with Falconeye's programming environment, whatever it may be, throwing some kind of exception if the query returns no value.

Still, I think I'd just select the count(1) without any having statement, and then test to see if it's less than 10. I think that would be more obvious and simple to maintain.

An interesting challenge might be to perform this test without counting every row in the table. Might it be faster, in some circumstances to retrieve the first 10 rows from the table and raise a flag when/if 10 rows are actually retrieved? I suspect that the overhead involved in pulling each row across might make it not worth it, but it's a thought...

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[FALCONSEYE]

Thanks. I am new to Oracle. I have used SQL Server 2000/2005 for over 7 year. Anyway, I am working on an application where I have to check out how many times a certain ip address submitted a particular form. So,

  select count(1) as appCountByIP
  from myLogTable
  where ipAddress = '1.1.1.1'
  and count(1) > 1

tells me
Error at line 4
ORA-00934: group function is not allowed here

Script Terminated on line 11.

where

 select count(1) as appCountByIP
  from myLogTable
  where ipAddress = '1.1.1.1'
  having count(1) > 1

works. In sql server, the first one would just fine.

http://www.fuzzysiberians.com

 

[SantaMufasa]

Yes, Falcon, your latter code is how Oracle expects to see your syntax.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[cmmrfrds]

I don't know if it is more efficient, but to avoid the having (aggregation overhead) this syntax should work to return the count and stop at however many rows you want.

select count(*) Over() as appCountByIP
from dual
where 1=1 and rownum < 2

 

[yelworcm]

Er...Not to rain on the parade, but SQL Server returns this:

Server: Msg 147, Level 15, State 1, Line 1
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

 

[SantaMufasa]

Not to rain on the parade...

Any rain that clears the skies is welcome. (I was wondering how SQL Server could process an aggregate function in a WHERE clause.)

Thanks, Yelworcm.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”