Home » SQL & PL/SQL » SQL & PL/SQL » total count (Oracle, 10g, Windows Server 2003)
total count [message #444614] Tue, 23 February 2010 00:57 Go to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi,
Consider following scenario

Below is the table creation script


SQL> Create table t1(num1 number,rating varchar2(5));

Table created

SQL> Create table t2(num1 number,rating varchar2(5));

Table created


Data insertion script is shown below


SQL> Insert Into t1 Values(1000,'AB');

1 row inserted

SQL> Insert Into t1 Values(1000,'CD');

1 row inserted

SQL> Insert Into t2 Values(1000,'AB');

1 row inserted

SQL> Insert Into t2 Values(1000,'CD');

1 row inserted

SQL> Insert Into t1 Values(2000,'CD');

1 row inserted

SQL> Insert Into t2 Values(3000,'CD');

1 row inserted


I executed following query on sql developer

SQL> SELECT T1.NUM1 "T1.NUM1",T1.RATING "T1.RATING",T2.NUM1 "T2.NUM1",T2.RATING "T2.RATING"
FROM T1 FULL OUTER JOIN T2
ON (T1.NUM1 = T2.NUM1
AND T1.RATING = T2.RATING);


[b]T1.NUM1 T1.RATING T2.NUM1 T2.RATING[/b]   
      1000 AB           1000 AB
      1000 CD           1000 AB
      2000 CD           null null
      null null         3000 CD


My requirement is, can get count of rows from same query like following output

[b]T1.NUM1 T1.RATING T2.NUM1 T2.RATING  Count_of_rows[/b]   
      1000 AB           1000 AB                   4
      1000 CD           1000 AB                   4
      2000 CD           null null                 4
      null null         3000 CD                   4 




Regards,
Ritesh

[Updated on: Tue, 23 February 2010 01:03]

Report message to a moderator

Re: total count [message #444615 is a reply to message #444614] Tue, 23 February 2010 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Cf. http://www.orafaq.com/forum/m/444239/102589/#msg_444239
I note that you didn't apply the solution that has been provided. Why?

Explain which count(s) you want. Show the result.

By the way, you don't need to show us the execution of the test case setting, just give the statements.
Also Bold or any other tags are not available in code.

Regards
Michel
Re: total count [message #444616 is a reply to message #444614] Tue, 23 February 2010 01:09 Go to previous messageGo to next message
pravin3032
Messages: 51
Registered: November 2006
Location: eARTH
Member
try using analytical function COUNT
Re: total count [message #444617 is a reply to message #444616] Tue, 23 February 2010 01:11 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Question is what count he wants? why? Is it total rows returned?

[Updated on: Tue, 23 February 2010 01:11]

Report message to a moderator

Re: total count [message #444648 is a reply to message #444614] Tue, 23 February 2010 03:51 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Google for COUNT() with windowing in Oracle.

regards,
Delna
Re: total count [message #444650 is a reply to message #444614] Tue, 23 February 2010 04:01 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select dname, count(*) over () cnt from dept order by 1;
DNAME                 CNT
-------------- ----------
ACCOUNTING              4
OPERATIONS              4
RESEARCH                4
SALES                   4

Regards
Michel
Previous Topic: How to avoid the max funtion
Next Topic: How to implement Trigger
Goto Forum:
  


Current Time: Sat Dec 03 19:53:34 CST 2016

Total time taken to generate the page: 0.27522 seconds