GROUP BY not working in my code

Question

I am trying to get the avg difference between order requested delivery date and actual delivery date, by Destination. The below is giving me issues. Do you know where I am going wrong? The message I am getting is "Each GROUP BY expression must contain at least on column that is not an outer reference"

SELECT CONCAT(Locations.City, ', ', Locations.State) AS Destination,
       AVG(DATEDIFF("Day", Orders.ReqDate, Orders.SchedDate)) AS "Average Diff"
FROM dbo.Orders,
     dbo.Locations
WHERE dbo.Orders.OrderNum= dbo.Locations.OrderNum
GROUP BY 'Destination'

possible duplicate of [When to use single quotes, double quotes, and backticks?](http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks) — fancyPants, Mar 18 '14 at 12:25

score 2 · Accepted Answer · answered Mar 18 '14 at 12:34

There are several ways to improve your query. I would suggest:

SELECT CONCAT(l.City, ', ', l.State) AS Destination,
       AVG(DATEDIFF(Day, o.ReqDate, o.SchedDate)*1.0) AS "Average Diff"
FROM dbo.Orders o join
     dbo.Locations l
     ON o.OrderNum = l.OrderNum
GROUP BY CONCAT(l.City, ', ', l.State) ;

Note the following:

"Day" --> Day. Day is a keyword in SQL Server and does not need quotes.
The use of table aliases, o and l to make the query more readable.
The multiplication by 1.0. DATEDIFF() returns an integer. The average of an integer (in SQL Server) is an integer. Usually, you would want a decimal number. You can also convert this to float or to another format, but I find *1.0 to be the simplest way for a fast conversion.
Fixed the GROUP BY (as noted in other answers).

score 1 · Answer 2 · answered Mar 18 '14 at 12:26

Try

SELECT CONCAT(Locations.City, ', ', Locations.State) AS Destination,
       AVG(DATEDIFF("Day", Orders.ReqDate, Orders.SchedDate)) AS "Average Diff"
FROM dbo.Orders,
     dbo.Locations
WHERE dbo.Orders.OrderNum= dbo.Locations.OrderNum
GROUP BY CONCAT(Locations.City, ', ', Locations.State)

score 1 · Answer 3 · answered Mar 18 '14 at 12:26

You are trying to group using a string value 'Destination', try changing it to use the expression CONCAT(Locations.City, ', ', Locations.State) and it should work:

SELECT CONCAT(Locations.City, ', ', Locations.State) AS Destination,
       AVG(DATEDIFF("Day", Orders.ReqDate, Orders.SchedDate)) AS "Average Diff"
FROM dbo.Orders,
     dbo.Locations
WHERE dbo.Orders.OrderNum= dbo.Locations.OrderNum
GROUP BY CONCAT(Locations.City, ', ', Locations.State)

GROUP BY not working in my code

3 Answers3