Combining two or more different SELECT queries to same table with different conditions in PostgreSQL

Question

I need to retrieve two different results from the same table, and combine them into a single result set. I'm using aggregate functions to generate reports on some data, and each column has different "where" conditions.

select sum(price) 
as lucro_esperado 
from tasks 
where extract(month from enddate) = 12 
AND extract(year from enddate) = 2019

and

select count(*) 
as tarefas_abertas 
from tasks 
where extract(month from date_added) = 12 
AND extract(year from date_added) = 2019

Since all I'm interested in this case are the aggregate functions results, I'm unable to use Join statements (as there's no ON condition), and Union ones will complain about different data types, as it's trying to wrongfully merge both aggregate results into a single column. Any other way I can achieve this, without having to query the database twice from my Node.js endpoint, and combining them manually?

Gordon Linoff · Accepted Answer · 2020-01-01T21:25:20.193

1

Just write this as one query:

select sum(price) as lucro_esperado, count(*) as tarefas_abertas
from tasks
where extract(month from enddate) = 12 and
      extract(year from enddate) = 2019

I would advise you to change the where clause to:

where enddate >= '2019-12-01' and
      enddate < '2020-01-01'

This allows the database to use an index on enddate (if available). Also, removing the function calls on the column helps the optimizer.

EDIT:

I see, the two date parameters are different. Just use conditional aggregation:

select sum(case when enddate >= '2019-12-01' and enddate < '2020-01-01' then price end) as lucro_esperado,
       sum(case when date_added >= '2019-12-01' and date_added < '2020-01-01' then 1 else 0 end) as tarefas_abertas
from tasks;

edited Jan 01 '20 at 21:25

answered Jan 01 '20 at 19:37

Gordon Linoff

1,242,037
58
646
786

I cannot since I need sum(price) to use the enddate parameter (for payments that are due this month) and count(*) to use the date_added parameter (for tasks that were added this month), since these two collumns are not necessarily always within the same month (eg a task added on 11/28, that's due 12/15) – Rafael Oliveira Jan 01 '20 at 20:04
@RafaelOliveira . . . I see. Those are *different* dates. Just use conditional aggregation. – Gordon Linoff Jan 01 '20 at 21:25
I have absolutely no idea of what you did there sir, but works like a charm! Thanks a lot! – Rafael Oliveira Jan 01 '20 at 22:12

Erwin Brandstetter · Answer 2 · 2020-01-02T04:08:22.040

Use the aggregate FILTER clause for conditional aggregation. And keep count(*) like you had it. That's shorter and faster:

SELECT sum(price) FILTER (WHERE enddate    >= '2019-12-01' AND enddate    < '2020-01-01') AS lucro_esperado
     , count(*)   FILTER (WHERE date_added >= '2019-12-01' AND date_added < '2020-01-01') AS tarefas_abertas
FROM   tasks;

How can I simplify this game statistics query?

And it's typically faster, yet, if you repeat the superset of conditions in an outer WHERE clause to eliminate rows early and put indexes to good use:

SELECT sum(price) FILTER (WHERE enddate    >= '2019-12-01' AND enddate    < '2020-01-01') AS lucro_esperado
     , count(*)   FILTER (WHERE date_added >= '2019-12-01' AND date_added < '2020-01-01') AS tarefas_abertas
FROM   tasks
WHERE  enddate    >= '2019-12-01' AND enddate    < '2020-01-01'
OR     date_added >= '2019-12-01' AND date_added < '2020-01-01';

Depending on the actual data type of enddate and date_added, you may want to adjust the input to remove dependency on the current time zone setting of your session. See:

Ignoring time zones altogether in Rails and PostgreSQL

Combining two or more different SELECT queries to same table with different conditions in PostgreSQL

2 Answers2