How can I return multiple where clauses in different rows in Postgresql?-CodePudding

I have two queries that count the total of employees according to multiple conditions; the only thing that changes is the last two AND clauses; I don't know how I can return the results in the same query. First Query

SELECT 
    COUNT(*)
FROM
(
SELECT
    E.NAME,
    E.LAST_NAME,
    E.BIRTH_DATE,
    E.ID
FROM
    EMPLOYEES E
WHERE E.BIRTH_DATE BETWEEN '2022-10-18 00:00:00' AND '2022-10-18 23:59:59'
AND E.NAME IS NOT NULL 
AND E.LAST_NAME IS NOT NULL
GROUP BY E.NAME, E.LAST_NAME, E.BIRTH_DATE,E.ID
) AUX;

Second Query

SELECT 
    COUNT(*)
FROM
(
SELECT
    E.NAME,
    E.LAST_NAME,
    E.BIRTH_DATE,
    E.ID
FROM
    EMPLOYEES E
WHERE E.BIRTH_DATE BETWEEN '2022-10-18 00:00:00' AND '2022-10-18 23:59:59'
AND E.NAME IS NULL 
AND E.LAST_NAME IS NULL
GROUP BY E.NAME, E.LAST_NAME, E.BIRTH_DATE,E.ID
) AUX;

Expected output:

total
3 --first row
5 --second row

CodePudding user response：

Quickest method (ie, least amount of re-writing) is simply UNION your queries together like this:

select 'null names' as col1, count(*)
from employees e
where e.name is null
  and e.last_name is null
union
select 'not null', count(*)
from employees e
where e.name is not null
  and e.last_name is not null;

col1	count(*)
null names	1
not null	3

You could also use conditional aggregation, which uses less real estate and quite easy to read, and produce output in columns with this....

select 
  sum(case when e.name is null and e.last_name is null then 1 else 0 end) as null_names, 
  sum(case when e.name is not null and e.last_name is not null then 1 else 0 end) as not_null
from employees e;

null_names	not_null
1	3

CodePudding user response：

SELECT count(*) filter (WHERE e.name IS NOT NULL AND e.last_name IS NOT NULL) as named
     , count(*) filter (WHERE e.name IS NULL AND e.last_name IS NULL) as unnamed
  FROM employees e
 WHERE e.birth_date BETWEEN '2022-10-18 00:00:00' AND '2022-10-18 23:59:59'
;

named	unnamed
3	5

Unlike a union, this does not require two separate queries. Only a single pass through the result set would be required.

But you asked for the results on separate rows, so...

SELECT unnest(ARRAY [
         count(*) filter (WHERE e.name IS NOT NULL AND e.last_name IS NOT NULL)
       , count(*) filter (WHERE e.name IS NULL AND e.last_name IS NULL)
       ])
  FROM employees e
 WHERE e.birth_date BETWEEN '2022-10-18 00:00:00' AND '2022-10-18 23:59:59'
;

unnest
3
5

This makes the two results into an array and then unnests the result into separate rows while still keeping the same query efficiency.