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

Question

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

and E.ID = 1 AND E.ID = 100... assuming this is an employee id, how could a row have both an ID of 1 and 100? Does this query return anything? Should it be and E.ID in (1, 100)? — Isolated
– Isolated, Commented Oct 26, 2022 at 15:25
@Isolated sorry, I forgot to erase that part, the query is without that condition — Mikasa
– Mikasa, Commented Oct 26, 2022 at 15:28
You can refactor your query to use conditional aggregation or simply union the results. — Stu
– Stu, Commented Oct 26, 2022 at 15:28

Isolated · Accepted Answer · 2022-10-26 15:41:08Z

0

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

answered Oct 26, 2022 at 15:41

Isolated

6,4942 gold badges12 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Miles Elam · Accepted Answer · 2022-10-26 18:23:39Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related