I'm studying T-SQL. I use Adventureworks database. Microsoft SQL Server Developer 16.0.1000.6

My task:

Write a query that ranks the salespeople based on the number of orders placed by the customers they are assigned to.

My query:

select C.SalesPerson, 
  count(SOH.SalesOrderID) over (partition by C.SalesPerson) as SalesCount,
  rank() over (order by count(SOH.SalesOrderID)) as [Rank]
from SalesLT.Customer as C
inner join SalesLT.SalesOrderHeader as SOH on C.CustomerID = SOH.CustomerID
group by
  C.SalesPerson
order by
  rank;

Error:

Column 'SalesLT.SalesOrderHeader.SalesOrderID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

1. Why does the error appear?
2. Why can't you use partition by in this line of code count(SOH.SalesOrderID) over (partition by C.SalesPerson) as SalesCount,

I'm studying T-SQL. I use Adventureworks database. Microsoft SQL Server Developer 16.0.1000.6

My task:

Write a query that ranks the salespeople based on the number of orders placed by the customers they are assigned to.

My query:

select C.SalesPerson, 
  count(SOH.SalesOrderID) over (partition by C.SalesPerson) as SalesCount,
  rank() over (order by count(SOH.SalesOrderID)) as [Rank]
from SalesLT.Customer as C
inner join SalesLT.SalesOrderHeader as SOH on C.CustomerID = SOH.CustomerID
group by
  C.SalesPerson
order by
  rank;

Error:

Column 'SalesLT.SalesOrderHeader.SalesOrderID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

1. Why does the error appear?
2. Why can't you use partition by in this line of code count(SOH.SalesOrderID) over (partition by C.SalesPerson) as SalesCount,

• sql-server
• t-sql

• This seems to be a SQL-Server limitation. Other SQL dialects like MySQL handle this scenario better. stackoverflow/questions/13999817/… – Bart McEndree Commented Feb 3 at 14:13
• Telling us what version of SQL Developer you use is like checking the dashboard radio for the software version there when we ask which engine you have in your car. – Joel Coehoorn Commented Feb 3 at 14:15
• 1 @BartMcEndree Wouldn't say MySQL/MariaDB handles it better, it's generally recommended to turn on only_full_group_by, and all other engines don't allow it either. – Charlieface Commented Feb 3 at 14:16
• Your query makes as little sense as bicycle to a fish. Change to: count(SOH.SalesOrderID) /*or just COUNT(*)*/ as SalesCount to make it work – siggemannen Commented Feb 3 at 14:22
• I have to agree with @charlieface on this one. The SQL standard does allow more freedom than permitted by SQL Server, if the grouped column can show functional dependency via primary key, but MySQL takes it way too far and fails to enforce it well at all, which is worse. – Joel Coehoorn Commented Feb 3 at 14:23

1 Answer 1

In the SalesCount, you are asking it to do a windowed count of non-null SalesOrderID values. But windowing functions are executed after grouping and normal aggregation, so the SalesOrderID column doesn't exist anymore.

Remember that the logical order of SQL execution is:

• FROM/JOIN/APPLY
• WHERE
• GROUP BY
• HAVING
• OVER (window functions)
• SELECT
• DISTINCT/UNION/EXCEPT/INTERSECT
• ORDER BY
• TOP/OFFSET

You could in theory use:

  sum(count(SOH.SalesOrderID)) over (partition by C.SalesPerson) as SalesCount,

in other words: ask for the aggregated count, then do a windowed sum of that.

But it makes little sense to do so, because you are anyway grouping by C.SalesPerson, so the partition would always be exactly one row. So you may as well just do

  count(SOH.SalesOrderID) as SalesCount,

Equally, SalesOrderID is probably not null, so it's the same thing to do

  count(*) as SalesCount,

It's also the same thing to order by rank as to order by WhateverRankIsOrderedBy.

So your query can be simplified to

select
  C.SalesPerson, 
  count(*) as SalesCount,
  rank() over (order by count(*)) as [Rank]
from SalesLT.Customer as C
inner join SalesLT.SalesOrderHeader as SOH on C.CustomerID = SOH.CustomerID
group by
  C.SalesPerson
order by
  SalesCount;