Error Referencing Sub Query in Group By on Main Select statement in T-SQL

I am trying to run the following T-SQL code

SELECT 
    MONTH(ro.[Date processed at hub]), 
    DATENAME(mm, ro.[Date processed at hub]), 
    YEAR(ro.[Date processed at hub]), 
    COUNT(ro.[Date processed at hub]) as 'No of Orders', 
    COUNT(ro.[Date processed at hub]) * 5.72 as 'Cost', 
    (SELECT COUNT(ite.[Date return created]) 
     FROM [ZoomBI].[dbo].[ReboundOrder] Ite 
     WHERE ro.[Order Reference] = ite.[Order Reference]) AS NoofItems
FROM 
    [ZoomBI].[dbo].[ReboundOrder] ro 
GROUP BY 
    YEAR(ro.[Date processed at hub]), 
    DATENAME(month, ro.[Date processed at hub]), 
    MONTH(ro.[Date processed at hub])
ORDER BY
    MONTH(ro.[Date processed at hub])

Example:

Order table

OrderNumber   Date Processed
----------------------------
    123       12/09/2020

Item table

OrderNumber   SKU       ReturnCreated
-------------------------------------
    123       MS1234    12/09/2020
    123       MS1235    13/09/2020
    123       MS1235    02/10/2020

Result:

Month       Year   NoofOrders   NoofItems
-----------------------------------------
September   2020        1           2

I need to be able to count the number of items for that order per month and year however,

I am getting this error

Column ‘Order Reference’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Even though it is not used in the main select?

Any help appreciated

Answer

You are using it in the main SELECT, because it is being fed into the correlated subquery.

Instead, you can do this as a join, then sum that up. I’ve done this as an APPLY but you could also do a JOIN

SELECT 
  month(ro.[Date processed at hub]), 
  DATENAME(mm, ro.[Date processed at hub]), 
  YEAR(ro.[Date processed at hub]), 
  COUNT(*) as [No of Orders], 
  COUNT(*) * 5.72 as Cost,
  SUM(ite.NoofItems) AS NoofItems
FROM 
  [ZoomBI].[dbo].[ReboundOrder] ro 
CROSS APPLY (
    SELECT 
      COUNT(*) AS NoofItems
    FROM 
      [ZoomBI].[dbo].[ReboundOrder] Ite 
    WHERE 
      ro.[Order Reference] = ite.[Order Reference]
  ) AS ite
GROUP BY 
  YEAR(ro.[Date processed at hub]), 
  DATENAME(month, ro.[Date processed at hub]), 
  month(ro.[Date processed at hub])
order by 
  month(ro.[Date processed at hub])

Note that EOMONTH can be more efficient as a grouping value than YEAR and DATENAME, so try this

SELECT 
  month(EOMONTH(ro.[Date processed at hub])), 
  DATENAME(mm, EOMONTH(ro.[Date processed at hub])), 
  YEAR(EOMONTH(ro.[Date processed at hub])), 
  COUNT(*) as [No of Orders], 
  COUNT(*) * 5.72 as Cost,
  SUM(ite.NoofItems) AS NoofItems
FROM 
  [ZoomBI].[dbo].[ReboundOrder] ro 
CROSS APPLY (
    SELECT 
      COUNT(*) AS NoofItems
    FROM 
      [ZoomBI].[dbo].[ReboundOrder] Ite 
    WHERE 
      ro.[Order Reference] = ite.[Order Reference]
  ) AS ite
GROUP BY 
  EOMONTH(ro.[Date processed at hub])
order by 
  EOMONTH(ro.[Date processed at hub])

Side note: COUNT(NonNullValue) is the same as COUNT(*) or COUNT(1)