OraFAQ Forum: SQL & PL/SQL » no of counts from case when

Home » SQL & PL/SQL » SQL & PL/SQL » no of counts from case when

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

no of counts from case when [message #608488]

Thu, 20 February 2014 05:20

ashwanth77
Messages: 95
Registered: April 2013
Location: India

Member

select del_point,del_point1,case when del_point=del_point1 then 'true' else 'false',count(*) from tablename;

here i want to find no of true and false counts like true(40),false(50)

Report message to a moderator

Re: no of counts from case when [message #608490 is a reply to message #608488]

Thu, 20 February 2014 05:25

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

count counts not null values. So have two case statements, one that returns a value for the true condition and one that returns a value for the false condition, count each:

count(case when del_point=del_point1 then 1 end) count_true,
count(case when not del_point=del_point1 then 1 end) count_false

Report message to a moderator

Re: no of counts from case when [message #608499 is a reply to message #608490]

Thu, 20 February 2014 06:10

msol25
Messages: 396
Registered: June 2011

Senior Member

Hi,

Please find the query :

select   del_point
        ,del_point1
        ,count(case when (case  when   del_point = del_point1 
                                then   1
                                else  0       
                          end) = 1 
                    then  1
               end
              ) as true_res,
        ,count(case when (case  when   del_point = del_point1 
                                then   1
                                else  0       
                          end) = 0 
                    then  1
               end
              ) as false_res        
        ,count(1) total_rec
from    tablename
group by del_point
        ,del_point1

[Updated on: Thu, 20 February 2014 06:20]

Report message to a moderator

Re: no of counts from case when [message #608503 is a reply to message #608499]

Thu, 20 February 2014 06:18

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Please let people find by themselves, they then much better learn than with a built-in solution.

Report message to a moderator

Previous Topic:	Procedure.,
Next Topic:	alias name in where clause

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 00:05:33 CDT 2024