As a new user to Magento Cloud and Magento BI, I am attempting to convert a lot of queries for regular reports I would run directly on the database to BI SQL queries. I am having a lot of trouble with date aggregation functions. One example is this query which will give a breakdown of payment method by month.
SELECT extract(year from o.created_at) as "year", extract(month from o.created_at) as "month", p.method, sum(p.base_amount_paid) as "method_total", t.total as "total", sum(p.base_amount_paid)/t.total*100 as "method_percentage"
FROM sales_order o
LEFT JOIN sales_order_payment p ON p.parent_id = o.entity_id
LEFT JOIN (
SELECT extract(year from o.created_at) as "YEAR", extract(month from o.created_at) as "MONTH", sum(base_amount_paid) as "total"
FROM sales_order o
INNER JOIN sales_order_payment p ON p.parent_id = o.entity_id
WHERE o.created_at > '2019-10-02'
AND o.status = 'complete'
GROUP BY extract(year from o.created_at), extract(month from o.created_at)
LIMIT 100
) t on t."YEAR" = extract(year from o.created_at) AND t."MONTH" = extract(month from o.created_at)
WHERE created_at > '2019-10-02'
AND o.status = 'complete'
GROUP BY extract(year from o.created_at), extract(year from o.created_at), p.method
ORDER BY extract(year from o.created_at), extract(month from o.created_at) ASC
LIMIT 100
I think I have applied the necessary conversion of extract(year from .... etc from SQL to the Redshift syntax, but running this query I get the error:
ERROR: column "o.created_at" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: ... from o.created_at) as "year", extract(month from o.created_... ^
Whilst this report may be able to be achieved an easier way or with the visual report builder, the concept and error is what I'm struggling with. A lot of the reporting that I need to run needs to be grouped into year and month (for monthly reporting). I've had similar issues with other queries.
Any help is appreciated.
