Urgent: how to write SQL query???

Posted by Somasundaram under Sql Server on 6/7/2012 | Points: 10 | Views : 1041 | Status : [Member] | Replies : 7
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




Responses

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

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

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

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

Up
0
Down
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: CGN007 on: 6/7/2012 [Member] Silver | Points: 25

Up
0
Down
Please change the Column name 'rDesignation' to Designation and run your query,That works fine.

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 pvt


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

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

Up
0
Down

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

Posted by: CGN007 on: 6/23/2012 [Member] Silver | Points: 25

Up
0
Down
Mark as Answer if its helpful to you,that motivates...

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

Login to post response