How to get emp id, sal, date

Posted by Kk86kiran under Sql Server on 4/11/2012 | Points: 10 | Views : 2452 | Status : [Member] | Replies : 10

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

i have one table consists 3 fields empid,sal,date.

emp:

empid
sal
date

in this table ids are repeated means empid 1 3times and 2 4times and 3 6times
empid 1 2 3 1 2 3
sal 10000 2000 1200 2000 3000 2098
date 2012-1 2012-1 2012-1 2012-2 2012-2 2012-2

like the above table the data in the table.

Now i want to retrieve no of empid as eno,empid, sum of sal for each empid and dates

like the below table

empid eno sal dates

1 2 12000 2 months
2 2 5000 2 months
3 2 3298 2 months

k.m.j.kiran

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Sabarimahesh on: 4/12/2012 [Member] Bronze | Points: 25

0	use this query: SELECT empid, COUNT(empid) AS eno,SUM(sal) FROM Cms_assignedparameters GROUP BY empid HAVING (COUNT(empid) > 1) Life is a Race Thanks & Regards By Sabari Mahesh P M Kk86kiran, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Sabarimahesh on: 4/12/2012 [Member] Bronze | Points: 25

0	Kk86kiran If You Got ur Exact Requiement ... Please mark it As Answer Life is a Race Thanks & Regards By Sabari Mahesh P M Kk86kiran, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Sabarimahesh on: 4/12/2012 [Member] Bronze | Points: 25

0	Kk86kiran or Else Send me ur feedback.... Life is a Race Thanks & Regards By Sabari Mahesh P M Kk86kiran, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Kk86kiran on: 4/12/2012 [Member] Starter | Points: 25

0	Hi friends thanks for all ur replies but my requirement i should get empid and also no of empid with salary total of each employee from starting month to end month. for example the out put will be like this: empid eno(no of empid) Totalsal TotalMonths 1 4 20000 4 2 5 18900 5 3 3 10000 3 in the above out put empid is employee id , eno means repetition empid ( for every month an id will count) total sla means sum of sal and finally total months means for 4 months (jan,feb,mar,apr) like this. eno means in jan month empid 1 took sal, in feb mar apr months also empid took sal so eno means no of repetitions of id's k.m.j.kiran Kk86kiran, if this helps please login to Mark As Answer. \| Alert Moderator

Hi friends thanks for all ur replies but my requirement i should get empid and also no of empid with salary total of each employee from starting month to end month.
for example the out put will be like this:

empid eno(no of empid) Totalsal TotalMonths

1 4 20000 4
2 5 18900 5
3 3 10000 3

in the above out put empid is employee id , eno means repetition empid ( for every month an id will count) total sla means sum of sal and finally total months means for 4 months (jan,feb,mar,apr) like this.

eno means in jan month empid 1 took sal, in feb mar apr months also empid took sal so eno means no of repetitions of id's

k.m.j.kiran

Kk86kiran, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sabarimahesh on: 4/12/2012 [Member] Bronze | Points: 25

0	Kk86kiran Welcome man Life is a Race Thanks & Regards By Sabari Mahesh P M Kk86kiran, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Kk86kiran on: 4/12/2012 [Member] Starter | Points: 25

0	my face book id is kiranmyth or u can search with my gmail id i.e, kk86kiran@gmail.com k.m.j.kiran Kk86kiran, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Sabarimahesh on: 4/12/2012 [Member] Bronze | Points: 25

0	Kk86kiran ya Sure Life is a Race Thanks & Regards By Sabari Mahesh P M Kk86kiran, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Hariinakoti on: 4/12/2012 [Member] Starter | Points: 25

0	Select distinct Empid,Eno,Totalsal,Totalmonths from tablename Thanks & Regards Hari Kk86kiran, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Niladri.Biswas on: 4/17/2012 [Member] Platinum | Points: 25

0	Try this Declare @emp table (EmpId int, Salary int,SomeDate DateTime) Insert into @emp Select 1,10000,'2012-1-1' Union All Select 2,2000,'2012-1-1' Union All Select 3,1200,'2012-1-1' Union All Select 1,2000,'2012-2-1' Union All Select 2,3000,'2012-2-1' Union All Select 3,2098,'2012-2-1' Select EmpId ,eno = Count(EmpId) ,sal = Sum(Salary) ,dates = Cast(Max(Month(SomeDate)) as varchar(50)) + ' months' From @emp Group by EmpId /* Result/ EmpId eno sal dates 1 2 12000 2 months 2 2 5000 2 months 3 2 3298 2 months Best Regards, Niladri Biswas Kk86kiran, if this helps please login to Mark As Answer*. \| Alert Moderator

Try this

Declare @emp table (EmpId int, Salary int,SomeDate DateTime)

Insert into @emp 

Select 1,10000,'2012-1-1' Union All

Select 2,2000,'2012-1-1' Union All

Select 3,1200,'2012-1-1' Union All

Select 1,2000,'2012-2-1' Union All

Select 2,3000,'2012-2-1' Union All

Select 3,2098,'2012-2-1'





Select 

		EmpId

		,eno = Count(EmpId)

		,sal = Sum(Salary)

		,dates = Cast(Max(Month(SomeDate)) as varchar(50)) + ' months'

From @emp

Group by EmpId

/* Result*/

EmpId	eno	sal	dates

1	2	12000	2 months

2	2	5000	2 months

3	2	3298	2 months

Best Regards,
Niladri Biswas

Kk86kiran, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sabarimahesh on: 4/17/2012 [Member] Bronze | Points: 25

0	Niladri.Biswas Good explanation Life is a Race Thanks & Regards By Sabari Mahesh P M Kk86kiran, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts