Question 114044 in PostgreSQL Question posted in
The official documentation can be found here.

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

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
Tags:

2

Answers


  1. 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
    Login or Signup to reply.
  2. 0 
    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search