> Select Count(*) from T1 where a=10 3> Select count(*) from T1 where b=20 11Now, What will b the O/P of the following..select count(*) from T1 where a=10 or b=20..............................

It will give answer 14. as the number of rows returned by a=10 is 3 and b=20 is 11 . so in the last query the result will be 14.

raz

Oct 17th, 2006

it will display the sum of the result of both - the filed common to bothsuppose the value a=10 is present in one of the row then what it will do is sum of both the query -1

Pawan

Oct 17th, 2006

It will not display only the sum of both. it will display the number of occurences .

I've executed the above code @ oracle 9i, Result ---> 14

i,e on select with the condition (a=10 or b=20) returns 14 row.. n

select count(*) from T1 where a=10 or b=20;

14 ..............................

Thanks & Regards,

Shamim

Sapienz Solutions

dev

Oct 18th, 2006

Hi,

Yeah. the result will be 14. because we gave 'or' in the query.

thanks

dev

Arun

Oct 23rd, 2006

Consider this :

ENAME JOB DEPTNO SAL ---------- --------- ---------- ---------- SMITH CLERK 20 8900 ALLEN SALESMAN 30 5000 WARD SALESMAN 30 5000 JONES MANAGER 20 3175 MARTIN SALESMAN 30 5000 BLAKE MANAGER 30 3050 CLARK MANAGER 10 2650 SCOTT ANALYST 20 3200 KING PRESIDENT 10 5200 TURNER SALESMAN 30 5000 ADAMS CLERK 20 5000

JAMES CLERK 30 5000 FORD ANALYST 20 3200 MILLER CLERK 10 5000

SQL> select count(*) from emp where deptno = 10;

COUNT(*) ---------- 3

SQL> select count(*) from emp where sal = 5000;

COUNT(*) ---------- 7

SQL> select count(*) from emp where deptno = 10 or sal = 5000;

COUNT(*) ---------- 9

This is because 'OR' will compare both the statements and it checks for a 'TRUE' condition out of the two statements, if one out of them is 'TRUE' it will output a result.

examine the case of employee 'MILLER' he is both in department '10' and having a salary of 5000/-. While the statement 'where deptno = 10 or sal = 5000' is checked it evaluates DEPTNO first and finds it to be true. Since it already found a 'TRUE' the second part is skipped. hence it doesn't give a sum of both results but a common product. and result is not 3 + 7 = 10 but 9. Hope this is clear...

Nicey

Nov 29th, 2006

Hi I have executed this SQL codein Oracle 10g :select * from T1;A B10 2010 2010 2020 3030 2020 2030 2020 3020 2050 2020 2040 2030 20select count(*) from T1 where a=10COUNT(*)3select count(*) from T1 where b=20COUNT(*)11select count(*) from T1 where a=10 or b=20COUNT(*)11I got the result 11.. wats the difference in executing functions in 9i and 10g???

there is no diff in execution in 9i and 10g but what Mr Arun told is exactly right.The OR condition checks for true.So the number of trues is going to be the answer.

Annad

Jan 25th, 2007

The output will be 11 only not 14.

mojdeh

May 10th, 2007

It will be between 9-14 depends on data. If for any row both condition applies at the same time, one row counts.

Hi all I have practically proved it: and the ans is 14.

SQL> create table ab(a number, b number);

Table created.

SQL> insert into ab values(&a,&b); Enter value for a: 10 Enter value for b: 20 old 1: insert into ab values(&a,&b) new 1: insert into ab values(10,20)

1 row created.

SQL> / Enter value for a: 10 Enter value for b: 20 old 1: insert into ab values(&a,&b) new 1: insert into ab values(10,20)

1 row created.

SQL> / Enter value for a: 10 Enter value for b: 20 old 1: insert into ab values(&a,&b) new 1: insert into ab values(10,20)

1 row created.

SQL> / Enter value for a: 10 Enter value for b: 20 old 1: insert into ab values(&a,&b) new 1: insert into ab values(10,20)

1 row created.

SQL> / Enter value for a: 5 Enter value for b: 20 old 1: insert into ab values(&a,&b) new 1: insert into ab values(5,20)

1 row created.

SQL> / Enter value for a: a6 Enter value for b: 20 old 1: insert into ab values(&a,&b) new 1: insert into ab values(a6,20) insert into ab values(a6,20) * ERROR at line 1: ORA-00984: column not allowed here

SQL> / Enter value for a: 6 Enter value for b: 20 old 1: insert into ab values(&a,&b) new 1: insert into ab values(6,20)

1 row created.

SQL> select * from ab 2 ;

A B ---------- ---------- 10 20 10 20 10 20 10 20 5 20 6 20

6 rows selected.

SQL> select * from ab where a=10 2 ;

A B ---------- ---------- 10 20 10 20 10 20 10 20

SQL> select count(*) from ab where a=10 2 ;

COUNT(*) ---------- 4

SQL> select count(*) from ab where a=10 or b=20 2 ;

O/p will be 14. Because of OR condition in the where clause will result to count the number from a=10 as well as a=20, so 3+11=14 and it will return the result as 14.

In the case of or operator it will return sum of rows e.g. a=3,b=11 then answer is 14

Venkat

Jun 30th, 2012

The accurate answer is "The result can contain max of 14 records", but this not guaranteed. It can be less also. (Minimum is 10 rows and maximum is 14 rows). It is because there can be some overlpa in the same row between "a" and "b" column.

The answer will be between 11-14 . You have to consider two extreme cases where
1. 3 rows with a=10 and 11 rows with b=20 are all distinct rows - then the answer will be 14
2. Where there are 11 rows with b=10 and the 3 rows with a=10 are among those 11 rows - then the answer will be 11

So based on the number of overlaps the result may vary between 11-14

## > Select Count(*) from T1 where a=10 3> Select count(*) from T1 where b=20 11Now, What will b the O/P of the following..select count(*) from T1 where a=10 or b=20..............................

shamim909Profile Answers by shamim909 Questions by shamim909

Questions by shamim909 answers by shamim909

## Related Answered Questions

## Related Open Questions