Combine Two Select Queries in Postgresql

Combine multiple SELECT statements

Wrap individual sub-statements in parenthesis to make the syntax unambiguous:

(SELECT result FROM tbl1 LIMIT 1)
UNION ALL
(SELECT result FROM tbl2 LIMIT 1)

The manual about UNION is very clear on the matter:

select_statement is any SELECT statement without an ORDER BY, LIMIT,
FOR UPDATE, or FOR SHARE clause. (ORDER BY and LIMIT can be attached
to a subexpression if it is enclosed in parentheses. Without
parentheses, these clauses will be taken to apply to the result of the
UNION, not to its right-hand input expression.)

Combine two SELECT queries in PostgreSQL

Use a CTE to reuse the result from a subquery in more than one SELECT.

WITH cte AS (SELECT carto_id_key FROM table1 WHERE tag_id = 16)

SELECT carto_id_key
FROM   cte

UNION ALL
SELECT t2.some_other_id_key
FROM   cte
JOIN   table2 t2 ON t2.carto_id_key = ctex.carto_id_key

You most probably want UNION ALL instead of UNION. Doesn't exclude duplicates and is faster this way.

How can I combine the two select queries on the same table horizontally in Postgresql?

You can use analytic function ROW_NUMBER to rank records by increasing/decreasing sales for each product in a subquery, and then do conditional aggregation:

SELECT
    prod product,
    MAX(CASE WHEN rn2 = 1 THEN quant END) max_quant,
    MAX(CASE WHEN rn2 = 1 THEN cust END) max_cust,
    MAX(CASE WHEN rn2 = 1 THEN TO_DATE(year || '-' || month || '-' || day, 'YYYY-MM-DD') END) max_date,
    MAX(CASE WHEN rn2 = 1 THEN state END) max_state,
    MAX(CASE WHEN rn1 = 1 THEN quant END) min_quant,
    MAX(CASE WHEN rn1 = 1 THEN cust END) min_cust,
    MAX(CASE WHEN rn1 = 1 THEN TO_DATE(year || '-' || month || '-' || day, 'YYYY-MM-DD') END) min_date,
    MAX(CASE WHEN rn1 = 1 THEN state END) min_state,
    avg_quant
FROM (
    SELECT
        s.*,
        ROW_NUMBER() OVER(PARTITION BY prod ORDER BY quant) rn1,
        ROW_NUMBER() OVER(PARTITION BY prod ORDER BY quant DESC) rn2,
        AVG(quant) OVER(PARTITION BY prod) avg_quant
    FROM sales s
) x
WHERE rn1 = 1 OR rn2 = 1
GROUP BY prod, avg_quant

How to combine/merge two select queries by COLUMN in PostgreSQL?

A cross join would be very dangerous because it produces a huge amount of data. E.g. if Table1 and Table2 each have 1000 rows, the result of a cross join would be 1,000,000 rows!

However, I assume you want to line up matching rows. So use the following:

select  COALESCE(t1.id, t2.id) as id,
        t1.attr1, t1.attr2, t2.attr1, t2.attr2
from    Table1 t1
        full outer join Table2 t2 on
            t2.id = t1.id

The full outer join means this also returns rows where no match is found.

PostgreSQL - Merge two selects queries

You need the same number of columns for both selects, simply add a NULL column (you might have to cast it to a datatype):

   Select u.name as name,
          u.cpf as document,
          u.phone_number as phoneNumber,
          CAST(NULL AS VARCHAR(20)) as companyData,
          'false' as company
          FROM users u 
          WHERE company_id is NULL

   UNION

   Select c.name as name,
          c.cnpj as document, 
          c.phone_number as phoneNumber,
          c.company_data as companyData
          'true' as company
          FROM company c
          WHERE c.consumer = 'true'

  ORDER BY id

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

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;

Postgresql Combine two select statement with different number of columns

You can try something like this, so that both statements in the union have the same number of columns:

select tconst , attr_a , attr_b , attr_c, null attr_d, null attr_e
from table1

union all

select tconst , null attr_a , null attr_b , null attr_c, attr_d, attr_e
from table2

---

Related Topics