SELECT EXTRACT(YEAR_MONTH FROM created_at) AS ‘key’,
(SELECT SUM(tip_amount) FROM donation_order WHERE status = ‘paid’ AND EXTRACT(YEAR_MONTH FROM created_at) = EXTRACT(YEAR_MONTH FROM created_at)) AS ‘value’
FROM accounting_transaction
WHERE type IN (‘cost’, ‘commission’)
ORDER BY ‘key’;
Thanks for your quick reply. Let me answer your questions:
Yes, that’s correct.
Indeed, MYSQL.
Yes, it’s a time-based chart. I’m trying to do the following:
Creat a SQL query that sums up 2 columns from two different tables.
Column 1: Sum up the tip_amount column from the donation_order table where the status is ‘paid’.
Column 2: Sum up the amount from the accounting_transaction table where the type is ‘cost’ or ‘commission’.
Please let me know if you need any other information from my side.
So there is not any relations between accounting_transaction and donation_order?
So here is your first mistake, your looking twice at the same created_at you probably want to check that the donation_order.created_at is equal to accounting_transaction.created_at. So a more correct way to do this is:
EXTRACT(YEAR_MONTH FROM accounting_transaction.created_at) = EXTRACT(YEAR_MONTH FROM created_at)) AS 'value'
Then you don’t have any GROUP BY statement, which is probably required in your case if I get the idea.
I’m betting on this query at the end:
SELECT EXTRACT(YEAR_MONTH FROM created_at) AS 'key',
(SELECT SUM(tip_amount) FROM donation_order WHERE status = 'paid' AND EXTRACT(YEAR_MONTH FROM accounting_transaction.created_at) = EXTRACT(YEAR_MONTH FROM created_at)) AS 'value'
FROM accounting_transaction
WHERE type IN ('cost', 'commission')
GROUP BY 'key'
ORDER BY 'key';
Let me know if you still encounter the inital issue.
There is indeed no relationship between accounting_transaction and donation_order. The only thing I want to do is sumup the two columns and show them on a monthly basis in a graph
I’m getting the following error when adding that query:
I don’t think that you can even get some valid data in this case.
If you sum all tip_amount for a month then sum them on a condition WHERE type IN ('cost', 'commission') on another table accounting_transaction without any relationship you won’t get anything that makes much sense? You will probably sum multiple times the same tip_amount because there is no concrete link between checking accounting_transaction.type and the dotations.
That being said you probably need to use WITH statement to split your query into two parts to understand the structural issue of what you are trying to achieve.
WITH periods AS (
SELECT SUM(tip_amount) AS "sum", EXTRACT(YEAR_MONTH FROM created_at) as "month"
FROM donation_order
WHERE status = 'paid'
GROUP BY "month"
)
SELECT
periods.month AS key,
periods.sum AS value
FROM accounting_transaction
INNER JOIN periods
ON periods.month = EXTRACT(YEAR_MONTH FROM accounting_transaction.created_at)
WHERE type IN ('cost', 'commission')
GROUP BY periods.month,
ORDER BY key;
You need something to create a jointure between the two tables if not:
Your method is based on the matching of the created_at date (but you used the YEAR_MONTH from the date so even if records don’t match type IN ('cost', 'commission') and status = 'paid' they will be sum at the end…).
This might be a better approach if and only if the dates are exactly the same…
SELECT
EXTRACT(YEAR_MONTH FROM DO.created_at) AS key,
SUM(DO.tip_amount) AS value
FROM accounting_transaction AS AT
LEFT JOIN donation_order AS DO
ON DO.created_at = AT.created_at
WHERE AT.type IN ('cost', 'commission') AND DO.status = 'paid'
GROUP BY key,
ORDER BY key;
Not sure we can help you more than that at this point.
