If I group by the name that I assigned to a column (FullName), I get an error message. But if I group by "per.FirstName + ' ' + per.LastName" then everything works. Why does it happen?
The below query failed
SELECT per.FirstName + ' ' + per.LastName as FullName, format((Round(sum(had.totaldue),2)),'C') as total_sales
FROM SALES.SalesOrderHeader AS had
JOIN person.Person as peron per.BusinessEntityID = had.SalesPersonID
group by FullName
order by total_sales;
Query_1_result: An error message: Msg 207, Level 16, State 1, Line 13 Invalid column name 'FullName'
Instead of "FullName", I used the expression that was deriving it, and it worked.
SELECT per.FirstName + ' ' + per.LastName as FullName, format((Round(sum(had.totaldue),2)),'C') as total_sales
FROM SALES.SalesOrderHeader AS had
join person.Person as per
on per.BusinessEntityID = had.SalesPersonID
group by per.FirstName + ' ' + per.LastNameorder by total_sales;
Query_2_result: The table showing total sales amounts by each sales person.
Tried to add a job title column by joining another table to the above query, but it failed.
SELECT per.FirstName + ' ' + per.LastName as FullName,emp.JobTitle, format((Round(sum(had.totaldue),2)),'C') as total_sales
FROM SALES.SalesOrderHeader AS had
JOIN person.Person as per
on per.BusinessEntityID = had.SalesPersonID
JOIN HumanResources.Employee AS emp
on emp.BusinessEntityID = per.BusinessEntityIDgroup by per.firstname + ' ' + per.LastNameorder by total_sales;
Query_3_result: Msg 8120, Level 16, State 1, Line 3 Column 'HumanResources.Employee.JobTitle' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
