How write query for how many empls are joined in the perticuler year

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

I want write a query for displaying how many employees are joined in the particular year

Like    1998-6
        2000-8
        2012-10

Iam writing query it displays result like

1987-09-17 00:00:00.000 1
1989-09-21 00:00:00.000 1
1990-01-03 00:00:00.000 1
1991-05-21 00:00:00.000 1
1993-01-13 00:00:00.000 1
1994-06-07 00:00:00.000 4
1994-08-16 00:00:00.000 1
1994-08-17 00:00:00.000 1
1994-12-07 00:00:00.000 1

But i want to display in 1994-12-07 - 7 like this..

SQL

select  hire_date, count(Employee_Id)
  from  empp
group by hire_date

[edit]SHOUTING removed, Code block added - OriginalGriff[/edit]

Posted 26-Jul-14 0:55am

chandugg

Updated 26-Jul-14 0:57am

OriginalGriff

v2

Add a Solution

Comments

OriginalGriff 26-Jul-14 6:57am

DON'T SHOUT. Using all capitals is considered shouting on the internet, and rude (using all lower case is considered childish). Use proper capitalization if you want to be taken seriously.

Manoj Kumar Choubey 26-Jul-14 8:09am

select year( hire_date), count(Employee_Id)
from empp
group by year( hire_date)

CHill60 26-Jul-14 9:10am

Yes. Post as solution!

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Manoj Kumar Choubey · Accepted Answer · 2014-07-26T03:33:00

Solution 1

C#

select year( hire_date), count(Employee_Id)
from empp
group by year( hire_date)

Posted 26-Jul-14 3:33am

Manoj Kumar Choubey

Sandeep Singh Shekhawat · Accepted Answer · 2014-07-26T04:29:00

Hi Chandugg,

Please try following solution. In this solution I create a full example but you can see only final query.

SQL

--Create a Table for employee

Create Table empp
(Id int identity(1,1) primary key,
Employee_Id int,
hire_date datetime
)

---Insert data in table

insert into empp(Employee_Id,hire_date)
values (1,'1987-09-17 00:00:00.000'),
(1,'1989-09-21 00:00:00.000'),
(1, '1990-01-03 00:00:00.000'),
(1,'1991-05-21 00:00:00.000'),
(1,'1993-01-13 00:00:00.000'),
(4,'1994-06-07 00:00:00.000'),
(1, '1994-08-16 00:00:00.000'),
(1,'1994-08-17 00:00:00.000'),
(1,'1994-12-07 00:00:00.000')

--final query for expected result
Select DATEPART(yyyy,hire_Date), count(Employee_Id) from empp
Group by (DATEPART(yyyy,hire_Date))