Using SELECT UNION and returning output of two columns from one table

Question

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 WHERE ta.ActorGender in(‘m’)
UNION
Select COUNT(ActorGender) “Female Actors” 
from tblActor ta 
WHERE ta.ActorGender in(‘f’);

The output ends up being:

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

I want the output to look like:

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

I am looking for an alternative to go about this without using the CASE WHEN or THEN clauses.

Thanks in advance for the help as usual.

Solution 1

another way without using case:

select sum(males) as "Male Actors", sum(females) as "Female Actors" 
from 
(select count(actorGender) as Males, 0 as Females
from tblActor 
where actorGender = 'm'
union all
select 0 as males, count(actorGender) as Females
from tblActor
where actorGender = 'f')

should result in

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

Solution 2

Another way (without CASE expression):

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

and more solution with CROSS join:

SELECT m.MaleActors, f.FemaleActors
FROM 
  ( SELECT COUNT(*) AS MaleActors
    FROM tblActor 
    WHERE ActorGender = 'm' 
  ) m
  CROSS JOIN
  ( SELECT COUNT(*) AS FemaleActors
    FROM tblActor 
    WHERE ActorGender = 'f' 
  ) f  ;

Solution 3

This would do:

SELECT  COUNT(CASE WHEN ActorGender = 'm' THEN 1 ELSE NULL END) MaleActors,
        COUNT(CASE WHEN ActorGender = 'f' THEN 1 ELSE NULL END) FemaleActors
FROM tblActor 
WHERE ActorGender IN ('m','f')

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 
(
  select count(ActorGender) MaleActors, 'm' Gender
  from tblActor
  where ActorGender = 'm'
) m
cross join
(
  select count(ActorGender) FemaleActors, 'f' Gender
  from tblActor
  where ActorGender = 'f'
) f

View more solutions

Comments

[Admin]

Thank you sir, works like a charm. I only wish there was an alternative to go about this without using the CASE WHEN clauses...

[Lamak]

@learnenburn There is, you can use PIVOT, but for two elements, case seems easier to me

[Taryn]

@Lamak PIVOT will only work if it is Oracle 11g+ for anything else you have to use your version. :)

[Lamak]

@bluefeet You are right, my comment was more of an invitation for you to write said answer, actually ;-)

[Taryn]

@Lamak you asked, I answered. :)

[Admin]

Can you use only joins on this even though it's one table to get this answer? I haven't been taught CASE or PIVOT...

[Rafael Emshoff]

nice work-around, I had a similar problem where I needed to combine two unrelated queries into two adjacent columns... really nice work-around :P... I was already going mad, and it's so simple!

[Marco Demaio]

The 1st query on MySQL 5.7.23 works also without the ending "FROM DUAL"

[ypercubeᵀᴹ]

@MarcoDemaio correct. The question is tagged Oracle though (not mysql)

[Marco Demaio]

@ypercubeᵀᴹ I know, I gave you +1, I was only adding info of general interest :-)