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.
GROUP BY.GROUP BYclause required since you use aggregate function,SUM.