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!