Home » SQL & PL/SQL » SQL & PL/SQL » Simplify the Query
icon4.gif  Simplify the Query [message #360830] Mon, 24 November 2008 01:54 Go to next message
sivakumar.rj
Messages: 17
Registered: November 2008
Location: Chennai
Junior Member
I have a query in which where condition is to be implemented.
There is a common condition for both the query.

it will be like this...

1st Query :
-----------
select count(*) from tab1 a
where a.typ = 1;

2nd query :
----------
select count(*) from tab1 a
where a.typ = 1
and a.info_dt < = a.cut_dt;

I have used union all but instead of that is there anything.....
I want to have the both of the counts. Is it possible to make it in a single query....plz help me on this.....
Re: Simplify the Query [message #360834 is a reply to message #360830] Mon, 24 November 2008 01:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can use a CASE expression inside the COUNT function.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Simplify the Query [message #360836 is a reply to message #360834] Mon, 24 November 2008 02:07 Go to previous messageGo to next message
sivakumar.rj
Messages: 17
Registered: November 2008
Location: Chennai
Junior Member
Could you post the query.. for this...i could not getting it...
Re: Simplify the Query [message #360839 is a reply to message #360836] Mon, 24 November 2008 02:11 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

sivakumar.rj wrote on Mon, 24 November 2008 13:37
Could you post the query.. for this...i could not getting it...


Show us what did you try ? Just search for CASE and COUNT and try it .


Smile
Rajuvan
Re: Simplify the Query [message #360937 is a reply to message #360830] Mon, 24 November 2008 07:09 Go to previous messageGo to next message
danish_fsd@yahoo.com
Messages: 38
Registered: February 2008
Location: Pakistan
Member
Hello,

I think following sample query will help you.

select a.count1, b.count2
from
(select count(*) as count1 from emp where job='CLERK') a,
(select count(*) as count2 from emp where job='CLERK'
and sal<1000) b

Output is here.

COUNT1 COUNT2
---------- ----------
4 .......2

Regards

Danish Razz
Re: Simplify the Query [message #360939 is a reply to message #360937] Mon, 24 November 2008 07:15 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
This will require two passes through the table, whereas Michel's suggestion (use CASE within the COUNT) will require only one.
Previous Topic: parallel processing in PL/SQL
Next Topic: Oracle Job which calls a stored procedure taking too much time
Goto Forum:
  


Current Time: Sun Dec 04 18:53:49 CST 2016

Total time taken to generate the page: 0.12017 seconds