Getting errors in an sql query

Question

Problem -> Select all orders that are greater than the average for October 4.

Table Schema:

mysql> desc orders;
+-------+-------+------+-----+---------+-------+
| Field | Type  | Null | Key | Default | Extra |
+-------+-------+------+-----+---------+-------+
| ONUM  | int   | NO   | PRI | NULL    |       |
| AMT   | float | NO   |     | NULL    |       |
| ODATE | date  | NO   |     | NULL    |       |
| CNUM  | int   | YES  | MUL | NULL    |       |
| SNUM  | int   | YES  | MUL | NULL    |       |
+-------+-------+------+-----+---------+-------+

I have tried a number of queries for this(around 10), but still not able to get through. Some of the Queries that I have tried :

select outer.onum, outer.amt from orders outer where outer.amt>any(select avg(o.amt) from orders o group by o.odate having o.odate='1996-10-04') and outer.odate<>'1996-10-04';
select outer.onum, outer.amt from orders as outer where outer.amt>any(select avg(o.amt) as 04_avg from orders o group by o.odate having o.odate='1996-10-04') and outer.odate<>'1996-10-04';

Still, no success. Can anybody help me out? Plus, can anybody explain to the usage of aggregate function in relation to sub-query and the proper usage of correlated sub-queries and nested queries. Internet has not been much of help. Thank You.

Answer 1

You can get the average of 1996-10-04 with this query:

SELECT AVG(AMT) 
FROM orders
WHERE ODATE = '1996-10-04'

You can use the above query in the WHERE clause like this:

SELECT *
FROM orders
WHERE AMT > (
  SELECT AVG(AMT) 
  FROM orders
  WHERE ODATE = '1996-10-04'
) 

If you want to filter out rows of 1996-10-04 you can add:

AND ODATE <> '1996-10-04'