Trouble getting a count with union statements

1725 pts.
Tags:
SQL
How do I get a count from unioned statements, then subtract some of the values from a sum of the others? For example, say I have a table called EMPLOYEE, and I want to see how many more male employees I have than female employees. Therefore, I want:
COUNT(*) WHERE EMPLOYEE.GENDER = 'M'
-
COUNT(*) WHERE EMPLOYEE.GENDER = 'F' 
How can I write that

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • JimmyIT
    Thanks Frank
    1,725 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following