Using Select Union And Returning Output Of Two Columns From One Table

I am creating a query that counts the amount of male and female actors in my table. My current statement is as such: Select COUNT(ActorGender) “Male Actors” from (tblActor ta

Solution 1:

Another way (without CASE expression):

SELECT 
  ( SELECTCOUNT(*)
    FROM tblActor 
    WHERE ActorGender ='m' 
  ) AS MaleActors
, ( SELECTCOUNT(*)
    FROM tblActor 
    WHERE ActorGender ='f' 
  ) AS FemaleActors
FROM 
    dual ;

and more solution with CROSS join:

SELECT m.MaleActors, f.FemaleActors
FROM 
  ( SELECTCOUNT(*) AS MaleActors
    FROM tblActor 
    WHERE ActorGender ='m' 
  ) m
  CROSSJOIN
  ( SELECTCOUNT(*) AS FemaleActors
    FROM tblActor 
    WHERE ActorGender ='f' 
  ) f  ;

Solution 2:

This would do:

SELECTCOUNT(CASEWHEN ActorGender ='m'THEN1ELSENULLEND) MaleActors,
        COUNT(CASEWHEN ActorGender ='f'THEN1ELSENULLEND) FemaleActors
FROM tblActor 
WHERE ActorGender IN ('m','f')

Solution 3:

another way without using case:

selectsum(males) as "Male Actors", sum(females) as "Female Actors" 
from 
(selectcount(actorGender) as Males, 0as Females
from tblActor 
where actorGender ='m'unionallselect0as males, count(actorGender) as Females
from tblActor
where actorGender ='f')

should result in

Male Actors    Female Actors
-----------    -------------
7              21 

Solution 4:

If you are using Oracle 11g+, then you can use PIVOT:

select*from
(
  select actorgender
  from tblActor
) src
pivot
(
  count(actorgender)
  for actorgender in ('m' MaleActors, 'f' FemaleActors)
) piv

See SQL Fiddle with Demo

The result would be:

| MALEACTORS | FEMALEACTORS |
-----------------------------
|          4 |            5 |

Or you can use a CROSS JOIN to get the same result:

select m.MaleActors, f.FemaleActors
from 
(
  selectcount(ActorGender) MaleActors, 'm' Gender
  from tblActor
  where ActorGender ='m'
) m
crossjoin
(
  selectcount(ActorGender) FemaleActors, 'f' Gender
  from tblActor
  where ActorGender ='f'
) f