SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Here i am using pivot but unable to get count ??
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 03/06/2013 :  01:52:35  Show Profile  Reply with Quote
hello all,
i have wriiten a query with the help pivot table upto that result is coming fine
here is the query :

SELECT
 P.Name,p.EmpID,P.BranchID,[54] AS Active, [55] AS InActive,P.Ser,P.Cond,[56] AS Deleted, [57] AS Settled
FROM
(
Select E.EmpID,
E.FirstName+' '+E.LastName as Name,
E.BranchID,B.Cust_ID,
LAc.ProfileStatusID,
Count(B.IsSerious) as ser,
Count(B.IsConfidential) as Cond
from Emp_Details E join Cust_BasicInfo B on E.EmpID=B.ProfileOwnerEmpID join Cust_Login LAc on LAc.Cust_ID=B.Cust_ID group by B.IsSerious,B.IsConfidential,
E.EmpID,E.FirstName,E.LastName,E.BranchID,LAc.ProfileStatusID,B.Cust_ID
) s
PIVOT (COUNT(Cust_ID) FOR ProfileStatusID IN ([54],[55],[56],[57]))P



the colored one i am facing trouble

in result set :

Name	EmpID	BranchID	Active	InActive	Ser	Cond	Deleted	Settled
Chaitanya Teja	2	7	1	1	1	1	2	1
VBhaskaraReddy Kovvuri	3	7	4	2	1	1	1	2


but here i need to get full count of column ser and cond but it just showing 1 and 1
. actually for empid 2 and 3 having count of ser and cond around 4 and 6 but it is showing just 1 count....how to get full count suggest me

P.V.P.MOhan

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/06/2013 :  01:54:41  Show Profile  Reply with Quote

SELECT
 P.Name,p.EmpID,P.BranchID,[54] AS Active, [55] AS InActive,P.Ser,P.Cond,[56] AS Deleted, [57] AS Settled
FROM
(
Select E.EmpID,
E.FirstName+' '+E.LastName as Name,
E.BranchID,B.Cust_ID,
LAc.ProfileStatusID,
Count(B.IsSerious) OVER (PARTITION BY E.EmpID) as ser,
Count(B.IsConfidential) OVER (PARTITION BY E.EmpID) as Cond
from Emp_Details E join Cust_BasicInfo B on E.EmpID=B.ProfileOwnerEmpID join Cust_Login LAc on LAc.Cust_ID=B.Cust_ID group by B.IsSerious,B.IsConfidential,
E.EmpID,E.FirstName,E.LastName,E.BranchID,LAc.ProfileStatusID,B.Cust_ID
) s
PIVOT (COUNT(Cust_ID) FOR ProfileStatusID IN ([54],[55],[56],[57]))P


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 03/06/2013 :  02:00:30  Show Profile  Reply with Quote
visakh thanks....resolved it so easily

P.V.P.MOhan
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/06/2013 :  03:44:30  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000