How to get emp id, sal, date

Posted by Kk86kiran under Sql Server on 4/11/2012 | Points: 10 | Views : 1544 | Status : [Member] | Replies : 16
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


Responses

Posted by: Sakthi.Singaravel on: 4/11/2012 [Member] Silver | Points: 25

Up
0
Down
Hi kiran..

can u explain these bolded words:


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



Regards,
Singaravel M

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

Posted by: Sakthi.Singaravel on: 4/11/2012 [Member] Silver | Points: 25

Up
0
Down
i understand 2 months means that employee gets two months salary...

but can't understand after employee number, also came '2'..

Regards,
Singaravel M

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

Posted by: Sakthi.Singaravel on: 4/11/2012 [Member] Silver | Points: 25

Up
0
Down
i think its duplicate value of count...?

Regards,
Singaravel M

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

Posted by: Sakthi.Singaravel on: 4/12/2012 [Member] Silver | Points: 25

Up
0
Down
select empid, COUNT(empid) WHERE eno=empid, SUM(sal) FROM table_name





Regards,
Singaravel M

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

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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: Sakthi.Singaravel on: 4/12/2012 [Member] Silver | Points: 25

Up
0
Down
select empid, COUNT(empid) WHERE eno=empid, SUM(sal), COUNT(sal) FROM table_name

Regards,
Singaravel M

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

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Posted by: Sakthi.Singaravel on: 4/17/2012 [Member] Silver | Points: 25

Up
0
Down
@ Niladri.Biswas


thank u

Regards,
Singaravel M

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

Login to post response