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)
);
2
Answers
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:
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:
See a demo on MySQL 5.7