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