Urgent: how to write SQL query???

Posted by Somasundaram under Sql Server on 6/7/2012 | Points: 10 | Views : 2139 | Status : [Member] | Replies : 5

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

hi.,

I have table named Attendance whose fields are
Sno,SecurityGroup,SecurityName,Designation,AttendanceStatus
1, JJ, Ram,officer,present
2,JJ,Raja,Guards,Present
3,JJ,Rani,LadyGuards,Present
4,JJ,Ramu,officer,present

I need the Output as count of number of securities present in each Designation follows:
SecutityGroup, Officer,Guards,LadyGuards
JJ,2,1,1

To get this output how to write Sql Query

Thanks in Advance

With Regards,
Somasundaram

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Somasundaram on: 6/7/2012 [Member] Starter | Points: 25

0	I Tried out using PIVOT in SQL, But i could not Get count value.. Following is Code i tried: select SecurityGroup,Officer,Guards,LadyGuards from (select SecurityGroup,rDesignation from Attendance where SecurityGroup='jj') up PIVOT (count(Designation) for Designation IN (Officer,Guards,LadyGuards)) as pvt When i Execute this Query, I get SecurityGroup,Officer,Guards,LadyGuards JJ,0,0,0 Instead of, SecurityGroup,Officer,Guards,LadyGuards JJ,2,1,1 Thanks in Advance Regards, Somasundaram Somasundaram, if this helps please login to Mark As Answer. \| Alert Moderator

I Tried out using PIVOT in SQL, But i could not Get count value..
Following is Code i tried:

select SecurityGroup,Officer,Guards,LadyGuards from 

(select SecurityGroup,rDesignation from Attendance 

where SecurityGroup='jj') up

PIVOT (count(Designation) for Designation IN 

(Officer,Guards,LadyGuards)) as pvt

When i Execute this Query, I get
SecurityGroup,Officer,Guards,LadyGuards
JJ,0,0,0

Instead of,
SecurityGroup,Officer,Guards,LadyGuards
JJ,2,1,1

Thanks in Advance

Regards,
Somasundaram

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

Posted by: Somasundaram on: 6/7/2012 [Member] Starter | Points: 25

0	Where Does my Query Goes Wrong, Pls Help me.. Regards, Somasundaram Somasundaram, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Jeena on: 6/7/2012 [Member] Starter | Points: 25

0	Hi, When i executed your Query select SecurityGroup,Officer,Guards,LadyGuards from (select SecurityGroup,rDesignation from Attendance where SecurityGroup='jj') up PIVOT (count(Designation) for Designation IN (Officer,Guards,LadyGuards)) as pvt i got an error like " Invalid column name 'rDesignation'.".When i changed that to Designation i got the desired result. SecurityGroup,Officer,Guards,LadyGuards JJ,2,1,1 Is that not your expected result? Thanks Jeena Somasundaram, if this helps please login to Mark As Answer. \| Alert Moderator

Hi,

When i executed your Query

select SecurityGroup,Officer,Guards,LadyGuards from
(select SecurityGroup,rDesignation from Attendance
where SecurityGroup='jj') up
PIVOT (count(Designation) for Designation IN
(Officer,Guards,LadyGuards)) as pvt

i got an error like " Invalid column name 'rDesignation'.".When i changed that to Designation i got the desired result.

SecurityGroup,Officer,Guards,LadyGuards

JJ,2,1,1

Is that not your expected result?

Thanks
Jeena

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

Posted by: Naraayanan on: 6/7/2012 [Member] Starter | Points: 25

0	Hi , Check your Column name of Designation in your Table. Regards, Lakshmi Naraayanan.S http://dotnettechrocks.blogspot.in/ http://abaprocker.blogspot.com/ Somasundaram, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Muhsinathk on: 6/14/2012 [Member] Bronze | Points: 25

0	CREATE TABLE Attendance(Sno int not null PRIMARY KEY IDENTITY,SecurityGroup varchar(20),SecurityName varchar(20), Designation varchar(20),AttendanceStatus varchar(20)) INSERT INTO Attendance(SecurityGroup,SecurityName,Designation,AttendanceStatus) SELECT 'JJ', 'Ram','officer','present' UNION ALL SELECT 'JJ','Raja','Guards','Present' UNION ALL SELECT 'JJ','Rani','LadyGuards','Present' UNION ALL SELECT 'JJ','Ramu','officer','Present' SecutityGroup, Officer,Guards,LadyGuards JJ,2,1,1 SELECT SecurityGroup,Officer,Guards,LadyGuards FROM (SELECT SecurityGroup,Designation FROM Attendance WHERE SecurityGroup='jj') up PIVOT (COUNT(Designation) FOR Designation IN (Officer,Guards,LadyGuards)) AS pivt Somasundaram, if this helps please login to Mark As Answer. \| Alert Moderator

CREATE TABLE Attendance(Sno int not null PRIMARY KEY IDENTITY,SecurityGroup varchar(20),SecurityName varchar(20),
Designation varchar(20),AttendanceStatus varchar(20))

INSERT INTO Attendance(SecurityGroup,SecurityName,Designation,AttendanceStatus)
SELECT 'JJ', 'Ram','officer','present'
UNION ALL
SELECT 'JJ','Raja','Guards','Present'
UNION ALL
SELECT 'JJ','Rani','LadyGuards','Present'
UNION ALL
SELECT 'JJ','Ramu','officer','Present'
SecutityGroup, Officer,Guards,LadyGuards
JJ,2,1,1
SELECT SecurityGroup,Officer,Guards,LadyGuards FROM

(SELECT SecurityGroup,Designation FROM Attendance

WHERE SecurityGroup='jj') up

PIVOT (COUNT(Designation) FOR Designation IN

(Officer,Guards,LadyGuards)) AS pivt

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

Latest Posts