CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
user3276318
user3276318

Reputation: 3

I need a SQL Query which group by employee with year,

select datepart(yyyy,hiredate) as Hire_Date_of_Year,jobTitle,count(jobTitle) as Number_Of_Title
from [AdventureWorks2012].[HumanResources].[Employee]
group by jobTitle,hiredate
having hiredate like '2004%'
order by jobtitle asc

Above is my code.

the Output I am getting is this 

Hire_Date_of_Year   jobTitle         number_of_Count
2004                Buyer               1
2004                Buyer               1
2004                Buyer               1
2004                Buyer               1
2004                Buyer               1
2004                Buyer               1
2004                Buyer               1
2004                Janitor             1
2004                Janitor             1
2004                Janitor             1
2004                Janitor             1

The Output I am looking for 

Hire_Date_of_Year   jobTitle    number_of_Count
2004            Buyer               7
2004            Janitor             4

Thanks in Advance.

Upvotes: 0

Views: 841

Answers (4)

Heena Chhatrala
Heena Chhatrala

Reputation: 242

Try something like this:

SELECT jobTitle, Hire_Date_of_Year,SUM(number_of_Count)
FROM emp
GROUP BY jobTitle, Hire_Date_of_Year

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You shouldn't really do date comparisons using like. That is best used on strings. Here is a way to write the query you want:

select datepart(yyyy, hiredate) as Hire_Date_of_Year,
       jobTitle, count(jobTitle) as Number_Of_Title
from [AdventureWorks2012].[HumanResources].[Employee]
where datepart(yyyy, hiredate) = 2004
group by jobTitle,  datepart(yyyy, hiredate)
order by jobtitle asc;

If you want, the datepart(yyyy, hiredate) (or year(hiredate) if you like) in the group by is optional. If you don't include it, the select needs to put the year in an aggregation function, such as max(datepart(yyyy, hiredate)).

I moved the condition to the where clause for efficiency purposes. You can do the comparison after the aggregation (i.e. in the having clause). But that means the group by is grouping by all the years before doing the filtering.

Upvotes: 1

Rick S
Rick S

Reputation: 6586

Group by should be :

group by jobTitle,datepart(yyyy,hiredate)

Upvotes: 0

gvee
gvee

Reputation: 17171

You need to group by the Year, not the full date

SELECT DatePart(yy, hiredate) As Hire_Date_of_Year
     , jobTitle
     , Count(jobTitle) As Number_Of_Title
FROM   [AdventureWorks2012].[HumanResources].[Employee]
WHERE  DatePart(yy, hiredate) = 2004
GROUP
    BY DatePart(yy, hiredate)
     , jobTitle

Upvotes: 0

Related Questions