DATEADD() and subquery

Transact-SQL
1

I'm trying to write a line of code that looks something like this:

SELECT ShipCountry, AVG(Freight) AS Average_Freight, DATE(OrderDate)
FROM orders
GROUP BY ShipCountry
HAVING Average_Freight > 110
ORDER BY Average_Freight desc

The problem I'm having is that the average freight cost (OrderDate) needs to be in a 12 month range from today. How do I perform this operation?

2

SELECT ShipCountry, AVG(Freight) AS Average_Freight, DATE(OrderDate)
FROM orders
where DATE(OrderDate) >= getdate() and DATE(OrderDate) <= dateadd(month,+12,getdate())
GROUP BY ShipCountry
HAVING Average_Freight > 110
ORDER BY Average_Freight desc

3

I've attempted this, but it doesn't return anything.

4

Rule of thumb when using "group by" is to match the selected fields with "group by" field + aggregated fields.

I see you're using MySQL which doesn't have this "strict" policy when grouping.
In your case, the orderdate is neither "group by" nor aggregated, which means you can't predict the returned orderdate.
You should decide if you want to show the oldest orderdate (then use min function) or newest orderdate (then use max function). If you con't care which orderdate is shown, then why show it at all?

I'd probably write the query like this (I'm guessing you want to look at orders in the last year, even though you write "from today" which indicates you want to look at future orders):

select shipcountry
      ,avg(freight) as average_freight
      ,date(min(orderdate)) as orderdate
  from orders
 where orderdate>=date_add(curdate(),interval -1 year)
   and orderdate<date_add(curdate(),interval 1 day)
 group by shipcountry
 having avg(freight)>110
 order by average_freight desc
;

Btw.: This forum is dedicated to Microsoft SQL Server so you might get better help on a MySQL forum.