What is mean the condition COUNT(1) = 1 in oracle sql

Question

I have two table and write select:

SELECT A.code, B.account, A.ano 
FROM atable A, dtable B 
where A.ano=B.dno
group by A.code, B.account, A.ano 
having count(1)=1;

if I add condition having count(1)=1 I didn't get any result, why?

I guess you don't have `A.code_uv, B.account, A.cif_no` combinations that occur just once — HoneyBadger, Dec 22 '20 at 11:36
Side note: [Comparison between conventional syntax VS ANSI Syntax](https://stackoverflow.com/q/18891148/205233) — Filburt, Dec 22 '20 at 11:40
`count(1)` is just an eccentric way of writing `count(*)`. I'd advise sticking with the standard version. — William Robertson, Dec 22 '20 at 14:02

score 2 · Answer 1 · answered Dec 22 '20 at 11:41

Query groups data per columns listed in the GROUP BY clause. HAVING restricts result set to those whose count (for those grouped columns) equals 1.

If query returns nothing, it means that there's not at least one combination of those columns that has only one row as a result.

For example (based on Scott's EMP table):

SQL> select job, count(*) from emp
  2  group by job;

JOB         COUNT(*)
--------- ----------
CLERK              4
SALESMAN           4
PRESIDENT          1      --> only one person is a president
MANAGER            3
ANALYST            2

Your query:

SQL> select job
  2  from emp
  3  group by job
  4  having count(*) = 1;

JOB
---------
PRESIDENT

Let's set someone else to be a president:

SQL> update emp set job = 'PRESIDENT' where ename = 'ADAMS';

1 row updated.

The query doesn't return anything now (as there are 2 presidents):

SQL> select job
  2  from emp
  3  group by job
  4  having count(*) = 1;

no rows selected

SQL>

As of count(1): no point in it, use count(*) instead.

Thanks it is very clear and now I understand – Dovile Barkauskaite Dec 22 '20 at 12:05 — Dovile Barkauskaite, Dec 22 '20 at 12:05

What is mean the condition COUNT(1) = 1 in oracle sql

1 Answers1