CODEWITHSUNDEEP
sqloracle-databaseselectunion
user1438611
user1438611

Reputation:

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 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.

Upvotes: 4

Views: 24260

Answers (4)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115510

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  ;

Upvotes: 9

Taryn
Taryn

Reputation: 247620

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

Upvotes: 5

Hellion
Hellion

Reputation: 1740

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

Upvotes: 7

Lamak
Lamak

Reputation: 70638

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')

Upvotes: 8

Related Questions