September 1, 2017 at 12:57 am
Hello Database Experts !! I have to write a query using "AdventureWorks Database" which joins HumanResources.Employee table to the Person.Person table so that you can display the FirstName, LastName, and HireDate columns for each employee and JobTitle along with a count of employees for the title. So, i have written the following query select per.FirstName, per.LastName, emp.JobTitle, COUNT(*)Totalemployeeinthatjobtitle from Person.person per
inner join HumanResources.Employee emp
on emp.BusinessEntityID=per.BusinessEntityID Group by per.FirstName, per.LastName, emp.JobTitle
But, i am not getting desired output, though i am getting the same rows as the desired solution which is accomplished using the following derived table and CTE. But i am not getting the Total Employee Count in that particular job title , all i am getting is total employee count =1 for each row.
Using Derived TableSELECT FirstName, LastName, e.JobTitle, HireDate, CountOfTitle
FROM HumanResources.Employee AS e
INNER JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID
INNER JOIN (
SELECT COUNT(*) AS CountOfTitle, JobTitle
FROM HumanResources.Employee
GROUP BY JobTitle) AS j ON e.JobTitle = j.JobTitle;
Using CTE
WITH j AS (SELECT COUNT(*) AS CountOfTitle, JobTitle
FROM HumanResources.Employee
GROUP BY JobTitle)
SELECT FirstName, LastName, e.JobTitle, HireDate, CountOfTitle
FROM HumanResources.Employee AS e
INNER JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID
INNER JOIN j ON e.JobTitle = j.JobTitle;
What i want to know , is there is solution exist for above query in a simple T-SQL without using derived table and CTE , if it has, then where i am wrong in my query and why i am getting Total count 1 for each results. Besides this , what is derived table and CTE and i which scenario we have to use them. Specially that select statement inside any join and how it works. Please elaborate this in great detail which will be really helpful.
Thanks
September 1, 2017 at 5:35 am
The question you're attempting to answer consists of two data sets. One is the FirstName, etc, that comes from the straight forward part of the query joining Employee and Person. The other data set is the count of JobTitles. It's a completely different and independent data set. So, the best way to resolve something like that is using a derived table. A Common Table Expression (CTE) is just another form of a derived table. It's unique in that you can make a CTE that is recursive (calls itself), but other than that, either solution, defining a table through a SELECT or using a CTE works fine when you have a second data set that you have to define and then join to another data set.
I hope that helps.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 1, 2017 at 9:22 am
There's one more alternative:
SELECT per.FirstName,
per.LastName,
emp.JobTitle,
emp.HireDate,
COUNT(*) OVER(PARTITION BY emp.JobTitle) Totalemployeeinthatjobtitle
FROM Person.person per
INNER JOIN HumanResources.Employee emp on emp.BusinessEntityID = per.BusinessEntityID;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply