Hi,

I wouldn’t use UNION to do this: I would use CASE (or some other SQL construct that allows IF-THEN-ELSE logic). For example:

<pre>

SELECT SUM ( CASE

WHEN gender = ‘M’ THEN 1

WHEN gender = ‘F’ THEN -1

END

) AS m_balance

FROM employee;

</pre>

That may be the quickest way to get the answer you want. A more general approach is:

<pre>

SELECT m_total

, f_total

, m_total – f_total AS m_balance

FROM ( – Begin in-line view v to comute m_total, f_total

SELECT SUM ( CASE

WHEN gender = ‘F’ THEN 1

ELSE 0

END

) AS f_total

, SUM ( CASE

WHEN gender = ‘M’ THEN 1

ELSE 0

END

) AS m_total

FROM employee

) AS v – End in-line view v to comute m_total, f_total

;

</pre>

This can be modified as shown below if you really need to use UNION. In the solution below, WHERE is providing the IF-THEN-ELSE logic, so you don’t need CASE.

<pre>

SELECT m_total

, f_total

, m_total – f_total AS m_balance

FROM ( – Begin in-line view v1 to comute m_total, f_total (one row)

SELECT SUM (f_total) AS f_total

, SUM (m_total) AS m_total

FROM ( – Begin in-line view v2 to comute m_total, f_total (two rows)

SELECT COUNT (*) AS f_total

, 0 AS m_total

FROM employee

WHERE gender = ‘F’

UNION

SELECT 0 AS f_total

, COUNT (*) AS m_total

FROM employee

WHERE gender = ‘M’

) AS v2 – End in-line view v2 to comute m_total, f_total (two rows)

) AS v1 – End in-line view v1 to comute m_total, f_total (one row)

;

</pre>

Some products may not support CASE, but have somthing else that can be used to get the same result. For example, CASE was introduced into Oracle in version 8.1: before that, you could get the same result using DECODE.

## Discuss This Question: 1 Reply