Bavani

• Oct 13th, 2006

 

The output will be 11.

because the count function will count the maximum number of rows column.

sujata

• Oct 15th, 2006

 

it will ans 3

as when the where condition is evaluated in or case

if the first case is true it will not check for the other ok.

pawanvirgo

 Profile Answers by pawanvirgo 

• Oct 16th, 2006

 

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 .

Dilshaw

• Oct 17th, 2006

 

It varies between 11 and 14.

shamim909

 Profile Answers by shamim909 Questions by shamim909

• Oct 17th, 2006

 

Hi All,

 

      Thanks for your answers..

 

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???

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 5th, 2006

 

14 because in where clause any condition is true it will count

gangadharam.p

 Profile Answers by gangadharam.p Questions by gangadharam.p

• Dec 26th, 2006

 

Hi all,

   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.

krishnaindia2007

 Profile Answers by krishnaindia2007 

• May 8th, 2008

 

Count 14

khushi0508

 Profile Answers by khushi0508 

• May 14th, 2008

 

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  ;

  COUNT(*)
----------
         6

ramyaselvi16

 Profile Answers by ramyaselvi16 Questions by ramyaselvi16

• Jun 20th, 2008

 

Hi Shamin,

 I could not get the logic for the output of this Question. please explain detaily.

Rgds

Ramya

ramyaselvi16

 Profile Answers by ramyaselvi16 Questions by ramyaselvi16

• Jun 25th, 2008

 

Hi friends, I could not understand logic for this query result. anyone can tell explain here?

jabir.mkk

 Profile Answers by jabir.mkk 

• Oct 16th, 2009

 

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.

bbj1980

 Profile Answers by bbj1980 

• Apr 18th, 2011

 

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.

ChevyBlazer

 Profile Answers by ChevyBlazer 

• Jul 2nd, 2012

 

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