MySQL Common Table Expression Error Message

Teron 0
Viewed 65

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)
);
common-table-expression mysql sql
2 Answers
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;
Hosein Hamedan0

You don't need to give CTEs aliases at the end of their creation, ...group by t.created_at) tdt, and ...from total_daily_transactions) adt, this will lead to a 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.

If you are using MySQL 5.x, you can't use CTEs neither window functions; they are not supported. If you used them in this version you will get a syntax error. In this case you may try the following query:

SET @AVRG =(
    SELECT ROUND(AVG(daily_transactions), 0) 
    FROM 
    (
     SELECT created_at, SUM(amount) daily_transactions
     FROM transactions
     GROUP BY created_at
    ) T  );

SELECT created_at, SUM(amount) daily_transactions, @AVRG avg_transactions
FROM transactions
GROUP BY created_at
HAVING SUM(amount) > @AVRG;

See a demo on MySQL 5.7

userGpjR0
Related