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

Questions by shamim909   answers by shamim909

Showing Answers 1 - 9 of 9 Answers

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.

  Was this answer useful?  Yes

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

  Was this answer useful?  Yes

Pawan

  • Oct 17th, 2006
 

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

  Was this answer useful?  Yes

Dilshaw

  • Oct 17th, 2006
 

It varies between 11 and 14.

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

  Was this answer useful?  Yes

dev

  • Oct 18th, 2006
 

Hi,

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

thanks

dev

  Was this answer useful?  Yes

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

  Was this answer useful?  Yes

Annad

  • Jan 25th, 2007
 

The output will be 11 only not 14.

  Was this answer useful?  Yes

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.

  Was this answer useful?  Yes

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

  Was this answer useful?  Yes

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.

  Was this answer useful?  Yes

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.

  Was this answer useful?  Yes

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

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions