MySQL Common Table Expression Error Message-CodePudding

I wrote the below query with a CTE attempting to return all the dates on which daily transactions exceeded the average of all daily transactions; however, MySQL keeps returning an error message for something around line 1. I've attached a description of the transactions table as well as the query, any help would be greatly appreciated.

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'total_daily_transactions as (select t.created_at, ' at line 1

query:

with total_daily_transactions as
        (select
            t.created_at,
            sum(t.amount) as daily_transactions
        from transactions t
        group by t.created_at) tdt,
    avg_daily_transactions as
        (select
             round(avg(daily_transactions),0) as avg_transactions
        from total_daily_transactions) adt
select *
from total_daily_transactions tdt
join avg_daily_transactions adt
on tdt.daily_transactions > adt.avg_transactions;

transactions table:

CREATE TABLE transactions (
    id INT AUTO_INCREMENT,
    created_at DATE NOT NULL,
    posted_at DATE NOT NULL,
    transactions VARCHAR(255) NOT NULL,
    category VARCHAR(255) NOT NULL,
    sub_category VARCHAR(255) NOT NULL
        DEFAULT '',
    amount INT NOT NULL,
    alert VARCHAR(255)
        DEFAULT '',
    PRIMARY KEY (id)
);

CodePudding user response：

WITH total_daily_transactions
AS (SELECT t.created_at,
           SUM(t.amount) AS daily_transactions
    FROM transactions t
    GROUP BY t.created_at),
     avg_daily_transactions
AS (SELECT ROUND(AVG(daily_transactions), 0) AS avg_transactions
    FROM total_daily_transactions)
SELECT *
FROM total_daily_transactions tdt
    JOIN avg_daily_transactions adt
        ON tdt.daily_transactions > adt.avg_transactions;

CodePudding user response：

The syntax problem with your query is that you are giving the CTEs aliases at the end of their creation, ...group by t.created_at) tdt, and ...from total_daily_transactions) adt. Remove aliases to fix the syntax error.

Also, you may simplify your query as the following:

WITH avg_daily_transactions AS
(
  SELECT created_at, SUM(amount) daily_transactions
  FROM transactions
  GROUP BY created_at
)
SELECT D.created_at, D.daily_transactions, D.avg_transactions
FROM
(
  SELECT created_at, daily_transactions, ROUND(AVG(daily_transactions) OVER (), 0) avg_transactions
  FROM avg_daily_transactions
) D
WHERE D.daily_transactions > D.avg_transactions

See a demo.