how to use generate_series and union_all in this case?

41 Views Asked by At

basically I have two queries that are using generate_series to create sum by dates.

The first query is like this

SELECT gs_dates.date, sum(credit_card_transaction.amount) as credit_card_sales
FROM (
        select to_char(date_trunc('day', (current_date - date_series)), 'YY/MM/DD')
        AS date
        FROM generate_series(0, 15, 1)
        AS date_series
) gs_dates
LEFT OUTER JOIN (
    SELECT transaction.amount, created_at
    FROM transaction
    WHERE type = 'stripe'
    AND payment_status = 'paid'
) credit_card_transaction
ON (gs_dates.date=to_char(date_trunc('day', credit_card_transaction.created_at), 'YY/MM/DD'))
    GROUP BY gs_dates.date
    ORDER BY gs_dates.date;

which generate result like this

23/09/14,
23/09/15,
23/09/16,
23/09/17,
23/09/18,
23/09/19,
23/09/20,
23/09/21,
23/09/22,
23/09/23,
23/09/24,
23/09/25,
23/09/26,
23/09/27,
23/09/28,65
23/09/29,19.99

The second query

SELECT gs_dates.date, sum(usdc_transaction.token_value) as usdc_sales
FROM (
        select to_char(date_trunc('day', (current_date - date_series)), 'YY/MM/DD')
        AS date
        FROM generate_series(0, 15, 1)
        AS date_series
) gs_dates
LEFT OUTER JOIN (
    SELECT paymentintent.token_value, paymentintent.created_at
    FROM paymentintent
    WHERE status = 'completed'
) usdc_transaction
ON (gs_dates.date=to_char(date_trunc('day', usdc_transaction.created_at), 'YY/MM/DD'))
    GROUP BY gs_dates.date
    ORDER BY gs_dates.date;

which generates data like this

23/09/14,
23/09/15,
23/09/16,
23/09/17,
23/09/18,
23/09/19,
23/09/20,
23/09/21,
23/09/22,
23/09/23,
23/09/24,
23/09/25,
23/09/26,
23/09/27,
23/09/28,
23/09/29,0.065

I am trying to combine the two queries together by using union_all so they would sum up the total together by each individual date.... with this query


SELECT subquery.totalSalesQuery,  SUM(totalSalesQuery) as totalSales
FROM (
    (SELECT gs_dates.date, sum(usdc_transaction.token_value) as totalSalesQuery
      FROM (select to_char(date_trunc('day', (current_date - date_series)), 'YY/MM/DD')
                       AS date
            FROM generate_series(0, 15, 1)
                     AS date_series) gs_dates
               LEFT OUTER JOIN (SELECT paymentintent.token_value, paymentintent.created_at
                                FROM paymentintent
                                WHERE status = 'completed') usdc_transaction
                               ON (gs_dates.date = to_char(date_trunc('day', usdc_transaction.created_at), 'YY/MM/DD'))
      GROUP BY gs_dates.date
      ORDER BY gs_dates.date)
    UNION ALL
    (SELECT gs_dates.date, sum(credit_card_transaction.amount) as totalSalesQuery
    FROM (
            select to_char(date_trunc('day', (current_date - date_series)), 'YY/MM/DD')
            AS date
            FROM generate_series(0, 15, 1)
            AS date_series
    ) gs_dates
    LEFT OUTER JOIN (
        SELECT transaction.amount, created_at
        FROM transaction
        WHERE type = 'stripe'
        AND payment_status = 'paid'
    ) credit_card_transaction
    ON (gs_dates.date=to_char(date_trunc('day', credit_card_transaction.created_at), 'YY/MM/DD'))
        GROUP BY gs_dates.date
        ORDER BY gs_dates.date)
    ORDER BY 2
) subquery

but it is not working correctly... Does anyone know how do I fix this? or how do I combine the two queries together to sum the totals by each date using generate_series?

Thank you for answering!

0

There are 0 best solutions below