How to Use Multiple with Statements in One Postgresql Query

How to use multiple WITH statements in one PostgreSQL query?

Per the other comments the second Common Table Expression [CTE] is preceded by a comma not a WITH statement so

WITH cte1 AS (SELECT...)
, cte2 AS (SELECT...)
SELECT *
FROM
    cte1 c1
    INNER JOIN cte2 c2
    ON ........

In terms of your actual query this syntax should work in PostgreSql, Oracle, and sql-server, well the later typically you will proceed WITH with a semicolon (;WTIH), but that is because typically sql-server folks (myself included) don't end previous statements which need to be ended prior to a CTE being defined...

Note however that you had a second syntax issue in regards to your WHERE statement. WHERE date IN table_2 is not valid because you never actually reference a value/column from table_2. I prefer INNER JOIN over IN or Exists so here is a syntax that should work with a JOIN:

WITH table_1 AS (
SELECT GENERATE_SERIES('2012-06-29', '2012-07-03', '1 day'::INTERVAL) AS date
)

, table_2 AS (
SELECT GENERATE_SERIES('2012-06-30', '2012-07-13', '1 day'::INTERVAL) AS date
)

SELECT * 
FROM
     table_1 t1
     INNER JOIN 
     table_2 t2
     ON t1.date = t2.date
;

If you want to keep the way you had it which typically EXISTS would be better than IN but to to use IN you need an actual SELECT statement in your where.

SELECT * 
FROM
     table_1 t1
WHERE t1.date IN (SELECT date FROM table_2);

IN is very problematic when date could potentially be NULL so if you don't want to use a JOIN then I would suggest EXISTS. AS follows:

SELECT * 
FROM
     table_1 t1
WHERE EXISTS (SELECT * FROM table_2 t2 WHERE t2.date = t1.date);

Use multiple WITH tablename AS (…) statements using Postgres version 9.3

When you use CTE you only need to write with in the first CTE and use , comma to connect multiple CTE.

with currentyeargrade as ( 
      select ....
      from (...
             ...)t),
previousyeargrade as(
      select ....
      from (...
             ...)y)
select *
from currentyeargrade cyg
inner join previousyeargrade pvg on pvg.userid = cyg.userid

Here is a sample for you

CREATE TABLE T(col int);

insert into T values (1);

Query 1:

with currentyeargrade as  (SELECT * FROM T), 
previousyeargrade as (SELECT * FROM T)
select *
from previousyeargrade cyg
CROSS JOIN currentyeargrade pvg 

Results:

| col | col |
|-----|-----|
|   1 |   1 |

Postgres WITH query and multiple subsequent statements

Use multiple CTEs:

WITH users_to_delete AS (
      SELECT id
      FROM auth.user
      WHERE email IN ('address@email.com', 'address2@email.com')
     ),
     d1 AS (
      DELETE FROM schema1.table
      WHERE "userId" IN (SELECT id FROM users_to_delete)
      RETURNING *
     ),
     d2 AS (
      DELETE FROM schema2.table
      WHERE "userId" IN (SELECT id FROM users_to_delete)
      RETURNING *
     )
DELETE FROM schema3.table
WHERE "userId" IN (SELECT id FROM users_to_delete);

The returning clauses are not needed. I just find it strange to have CTEs that are entirely empty.

How to combine multiple select statements in postgresql?

Use conditional aggregation:

SELECT
    COUNT(*) FILTER (WHERE status = 'SENT') AS "SENT",
    COUNT(*) FILTER (WHERE status = 'NOT SET') AS "NOT SENT",
    COUNT(*) FILTER (WHERE status = 'INVALID NUMBER') AS "INVALID NUMBER"
FROM message_items;

If you wanted to generate counts over the entire table, then the above is suitable. If instead you want to generate counts per some other column, then just add that column to the above select clause and then aggregate by that column using GROUP BY.

postgresql run multiple select statements with different columns in single query

Just pad the columns you don't have and union. For instance:

select activities.type, sum(activity_fees.amount) 
...
Union
SELECT 'dummy', avg(activities.rating) 
....

Or just include activities.type since you have it available!

Pass multiple postgres SQL statements in a single PGexec call

I was able to accomplish what I was looking for with CTEs rather than temp tables... one long chain of CTEs (acting as temp tables) waterfalling into the main query.

A simple example:

with first as (
    select 1 as col
),
second as (
    select 2 as col
)
select * from first union all select * from second

A more complex example:

with COGS as (
    select 'Product1' Vertical, 3.0 Credit, 1.00 Debit, 2.75 Blend, 4.30 Amex,  0.25 ACH union
    select 'Product2',   3.1,   2.2,    2.8,    4.5,    0.25    union
), 
Allocable_Card_Volume as (
    select MPR.Date, sum(MPR.Card_Volume_Net_USD)  Allocable_Card_Volume
    from  mpr_base MPR  
    where MPR.Gateway in ('YapProcessing') and MPR.Vertical not in ('HA-Intl','HA')
    group by MPR.Date
),
COGS_Financials_Base as (
    select '2013-01-31'::DATE Date , 1000 Total_COGS , 200 Homeaway , (select Allocable_Card_Volume from Allocable_Card_Volume where Date in ('2013-01-31') ) Allocable_Card_Volume union
),
Initial_COGS as (
    select
        MPR.Date,
        sum(
        case    when    MPR.PaymentTypeGroup in ('ACH_Scan','AmEx') then (Txn_Count * COGS.ACH) else 0 end +
        case    when    MPR.Vertical not in ('HA') and MPR.PaymentTypeGroup in ('Card','AmEx-Processing') then
                                coalesce( ((Credit_Card_Net_USD - Amex_Processing_Net_USD) * COGS.Credit * 0.01),0) + coalesce((Debit_Card_Net_USD * COGS.Debit * 0.01),0) + coalesce((Amex_Processing_Net_USD * COGS.Amex * 0.01),0) + coalesce((case when TPV is null and PaymentTypeGroup in ('Card') then TPV_Billing else 0 end * COGS.Blend * 0.01),0)
                    when    MPR.Vertical in ('HA') and MPR.PaymentTypeGroup in ('Card','AmEx-Processing') and FeePaymentType in ('PropertyPaid') then
                                coalesce(COGS_Financials.Homeaway,0)
                                else 0 end
        ) Initial_COGS
    from
        mpr_base MPR
        left join COGS on COGS.Vertical = MPR.Vertical and MPR.Gateway in ('YapProcessing') and MPR.PaymentTypeGroup not in ('Cash')
        left join COGS_Financials_Base COGS_Financials on MPR.Date = COGS_Financials.Date and MPR.Gateway in ('YapProcessing') and MPR.PaymentTypeGroup in ('Card')
    where MPR.Gateway in ('YapProcessing') and MPR.Vertical not in ('HA-Intl') and MPR.PaymentTypeGroup not in ('Cash')
    group by
        MPR.Date
),
COGS_Financials as (
    select
        COGS_Financials_Base.*, (COGS_Financials_Base.Total_COGS - Initial_COGS.Initial_COGS) Allocation
    from
        COGS_Financials_Base
        join Initial_COGS on COGS_Financials_Base.Date = Initial_COGS.Date
),
MPR as (
    select
        MPR.Date,MPR.Gateway,MPR.Vertical, MPR.ParentAccountId, MPR.ParentName ,
        MPR.PaymentTypeGroup ,
        sum(TPV_USD) TPV_USD,
        sum(TPV_Net_USD) TPV_Net_USD,
        sum(Revenue_Net_USD) Revenue_Net_USD ,
        sum(coalesce(
            case    when MPR.PaymentTypeGroup in ('ACH_Scan','AmEx') then (Txn_Count * COGS.ACH) else 0 end +
            case    when MPR.Vertical not in ('HA') and MPR.PaymentTypeGroup in ('Card','AmEx-Processing') then
                    coalesce( ((Credit_Card_Net_USD - Amex_Processing_Net_USD) * COGS.Credit * 0.01),0) + coalesce((Debit_Card_Net_USD * COGS.Debit * 0.01),0) + coalesce((Amex_Processing_Net_USD * COGS.Amex * 0.01),0) + coalesce((case when TPV is null and PaymentTypeGroup in ('Card') then TPV_Billing else 0 end * COGS.Blend * 0.01),0)
                +(coalesce( ( ( cast(Card_Volume_Net_USD as decimal(18,2) ) / cast(COGS_Financials.Allocable_Card_Volume as decimal(18,2)) ) * COGS_Financials.Allocation  ), 0) ) -- Excess
                        when MPR.Vertical in ('HA') and MPR.PaymentTypeGroup in ('Card','AmEx-Processing') and MPR.FeePaymentType in ('PropertyPaid') then  coalesce(COGS_Financials.Homeaway,0)
                        else 0
          end,0)
        ) COGS_USD,
        sum(Txn_Count) Txn_Count
    from
        mpr_Base MPR
        left join COGS on COGS.Vertical = MPR.Vertical and MPR.Gateway in ('YapProcessing') and MPR.PaymentTypeGroup not in ('Cash')
        left join COGS_Financials on MPR.Date = COGS_Financials.Date and MPR.Gateway in ('YapProcessing') and MPR.PaymentTypeGroup in ('Card','AmEx-Processing')
    where
        MPR.Date in ('2016-02-29')
    group by
        MPR.Date,MPR.Gateway,MPR.Vertical  , MPR.ParentAccountId ,MPR.ParentName,
        MPR.PaymentTypeGroup 
)
select
    Vertical, 
    sum(TPV_USD)::money as TPV_USD,
    sum(Revenue_Net_USD)::money as Revenue_Net_USD,
    sum(COGS_USD)::money COGS_USD,
    round((sum(Revenue_Net_USD)-sum(COGS_USD))/sum(Revenue_Net_USD)*100,2) Accounting_Margin
from 
    MPR
where Date in ('2016-02-29')
group by
    Vertical
union all
select
    'Total' , 
    sum(TPV_USD)::money as TPV_USD,
    sum(Revenue_Net_USD)::money as Revenue_Net_USD,
    sum(COGS_USD)::money COGS_USD,
    round((sum(Revenue_Net_USD)-sum(COGS_USD))/sum(Revenue_Net_USD)*100,2) Accounting_Margin
from 
    MPR
where Date in ('2016-02-29')

I said it would be complex :-)

---

Related Topics