Joining two graphs of different tables

1

Hi everyone,

I’d like to join two graphs of two different tables, graph 1 plots the commission sum and the cashback sum of the last 13 months per month, and graph 2 does the same for pay out. However I’d rather want these three in 1 graph, but I can’t figure out how. The 2 working sql’s look as following;

SELECT str_to_date(concat(date_format(transactions.created_at, ‘%Y-%m’), ‘-01’), ‘%Y-%m-%d’) AS created_at, sum(transactions.commission) AS sum, sum(transactions.cashback) AS sum_2
FROM transactions
WHERE str_to_date(concat(date_format(transactions.converted_at, ‘%Y-%m’), ‘-01’), ‘%Y-%m-%d’) BETWEEN str_to_date(concat(date_format(date_add(now(), INTERVAL -13 month), ‘%Y-%m’), ‘-01’), ‘%Y-%m-%d’)
AND str_to_date(concat(date_format(now(), ‘%Y-%m’), ‘-01’), ‘%Y-%m-%d’)
GROUP BY str_to_date(concat(date_format(transactions.created_at, ‘%Y-%m’), ‘-01’), ‘%Y-%m-%d’)
ORDER BY str_to_date(concat(date_format(transactions.created_at, ‘%Y-%m’), ‘-01’), ‘%Y-%m-%d’) ASC

SELECT str_to_date(concat(date_format(payouts.exported_at, ‘%Y-%m’), ‘-01’), ‘%Y-%m-%d’) AS exported_at, sum(payouts.amount) AS sum
FROM payouts
WHERE str_to_date(concat(date_format(payouts.exported_at, ‘%Y-%m’), ‘-01’), ‘%Y-%m-%d’) BETWEEN str_to_date(concat(date_format(date_add(now(), INTERVAL -13 month), ‘%Y-%m’), ‘-01’), ‘%Y-%m-%d’)
AND str_to_date(concat(date_format(now(), ‘%Y-%m’), ‘-01’), ‘%Y-%m-%d’)
GROUP BY str_to_date(concat(date_format(payouts.exported_at, ‘%Y-%m’), ‘-01’), ‘%Y-%m-%d’)
ORDER BY str_to_date(concat(date_format(payouts.exported_at, ‘%Y-%m’), ‘-01’), ‘%Y-%m-%d’) ASC

is there any way to join graph 1 and 2, by joining the sql, or is there an easier way?

thanks in advance :grin:

2

The way I would do it would be to put them all in one query, union all different tables with date, sum and sum type (either with an extra with clauses or by splitting the first select into two) and then visualize the data with an extra x axis option for different sum types as in the pictures I added.
image

image
image1544×303 33.4 KB

Edit: I didn't really get into the code itself so this may be somewhat wrong but made a simple query that should most likely do the trick:

SELECT str_to_date(concat(date_format( transactions . created_at , ‘%Y-%m’), ‘-01’), ‘%Y-%m-%d’) AS date , sum( transactions . commission ) AS sum , 'sum' AS Sum_type
FROM transactions
WHERE str_to_date(concat(date_format( transactions . converted_at , ‘%Y-%m’), ‘-01’), ‘%Y-%m-%d’) BETWEEN str_to_date(concat(date_format(date_add(now(), INTERVAL -13 month), ‘%Y-%m’), ‘-01’), ‘%Y-%m-%d’)
AND str_to_date(concat(date_format(now(), ‘%Y-%m’), ‘-01’), ‘%Y-%m-%d’)
GROUP BY str_to_date(concat(date_format( transactions . created_at , ‘%Y-%m’), ‘-01’), ‘%Y-%m-%d’)
ORDER BY str_to_date(concat(date_format( transactions . created_at , ‘%Y-%m’), ‘-01’), ‘%Y-%m-%d’) ASC

UNION ALL

SELECT str_to_date(concat(date_format( transactions . created_at , ‘%Y-%m’), ‘-01’), ‘%Y-%m-%d’) AS date , sum( transactions . cashback ) AS sum , 'sum2' AS Sum_type
FROM transactions
WHERE str_to_date(concat(date_format( transactions . converted_at , ‘%Y-%m’), ‘-01’), ‘%Y-%m-%d’) BETWEEN str_to_date(concat(date_format(date_add(now(), INTERVAL -13 month), ‘%Y-%m’), ‘-01’), ‘%Y-%m-%d’)
AND str_to_date(concat(date_format(now(), ‘%Y-%m’), ‘-01’), ‘%Y-%m-%d’)
GROUP BY str_to_date(concat(date_format( transactions . created_at , ‘%Y-%m’), ‘-01’), ‘%Y-%m-%d’)
ORDER BY str_to_date(concat(date_format( transactions . created_at , ‘%Y-%m’), ‘-01’), ‘%Y-%m-%d’) ASC

UNION ALL

SELECT str_to_date(concat(date_format( payouts . exported_at , ‘%Y-%m’), ‘-01’), ‘%Y-%m-%d’) AS date , sum( payouts . amount ) AS sum, 'sum3' AS Sum_type
FROM payouts
WHERE str_to_date(concat(date_format( payouts . exported_at , ‘%Y-%m’), ‘-01’), ‘%Y-%m-%d’) BETWEEN str_to_date(concat(date_format(date_add(now(), INTERVAL -13 month), ‘%Y-%m’), ‘-01’), ‘%Y-%m-%d’)
AND str_to_date(concat(date_format(now(), ‘%Y-%m’), ‘-01’), ‘%Y-%m-%d’)
GROUP BY str_to_date(concat(date_format( payouts . exported_at , ‘%Y-%m’), ‘-01’), ‘%Y-%m-%d’)
ORDER BY str_to_date(concat(date_format( payouts . exported_at , ‘%Y-%m’), ‘-01’), ‘%Y-%m-%d’) ASC

3

yes that is what I tried, but I get errors every time. How would you do that

4

I added a code that should do the trick ( assuming the source code you provided works).

5

thanks, it worked.