Combining two separate queries result in one table

user_13634451 0
Viewed 32

I want to display how many employees have commission percentages and how many do not have.

enter image description here

These are the columns of the table. I tried to write two queries.

SELECT COUNT(*) COMMISSION_PCT 
FROM HR.EMPLOYEES 
WHERE COMMISSION_PCT IS NULL;

SELECT COUNT(*) NO_COMMISSION_PCT 
FROM HR.EMPLOYEES 
WHERE COMMISSION_PCT IS NOT NULL;

But I do not know how to combine those two queries into one result set.

oracle
1 Answers

You can use one of the queries below for your purpose:

SELECT 
  COUNT(CASE WHEN COMMISSION_PCT IS NULL THEN 1 END) AS COMMISSION_PCT,
  COUNT(CASE WHEN COMMISSION_PCT IS NOT NULL THEN 1 END) AS NO_COMMISSION_PCT
FROM HR.EMPLOYEES;

SELECT 
  SUM(IF(COMMISSION_PCT IS NULL, 1, 0)) AS COMMISSION_PCT,
  SUM(IF(COMMISSION_PCT IS NOT NULL, 1, 0)) AS NO_COMMISSION_PCT
FROM HR.EMPLOYEES;

I prefer the CASE WHEN over the SUM IF and tend to use it if I have to make a query for your purpose.

user_179018740
Related