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
 SQL Server 2012 Forums
 Transact-SQL (2012)
 SQL Query Group By
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mimuk
Starting Member

17 Posts

Posted - 12/05/2013 :  13:10:53  Show Profile  Reply with Quote
I hope someone can help with a Query/View I need to create.
I have table called Opportunity

CREATE TABLE [dbo].[Opportunity](
[Oppo_OpportunityId] [int] NOT NULL,
[Oppo_AssignedUserId] [int] NULL,
[Oppo_Stage] [nvarchar](40) NULL,

If I query the table the data is output as:
Select Oppo_OpportunityId, Oppo_AssignedUserID, Oppo_Stage from Opportunity
	
Oppo_OpportunityId			Oppo_AssignedUserID		Oppo_Stage
1						23			Closed		
2						23			Negotiating
3						23			Negotiating
4						24			Closed
5						25			Quoted
6						25			Closed
7						25			Closed
8						25			Closed
9						25			Lost


I need the data presented as follows:

AssignedUserID		Quoted		Negotiating	Lost	Closed		Total		Ratio 
23			0		2		0	1		3		33
24			0		0		0	1		1		100
25			1		0		1	3		5		60	


Ratio is calculated as the number of Closed / Total * 100

I hope this makes sense. I am really scratching my head on this.

Cheers,
Mim

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/05/2013 :  13:28:43  Show Profile  Reply with Quote

SELECT Oppo_AssignedUserID AS AssignedUserID,
[Quoted],[Negotiating],[Lost],[Closed],
[Quoted] + [Negotiating] + [Lost] + [Closed] AS Total,
[Closed]*100.0/[Quoted] + [Negotiating] + [Lost] + [Closed] AS Ratio
FROM
(
SELECT Oppo_AssignedUserID,Oppo_Stage,Oppo_OpportunityId
FROM [dbo].[Opportunity]
)t
PIVOT (COUNT(Oppo_OpportunityId) FOR Oppo_Stage IN ([Quoted],[Negotiating],[Lost],[Closed]))p


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

mimuk
Starting Member

17 Posts

Posted - 12/05/2013 :  14:53:52  Show Profile  Reply with Quote
HI visakh16, thats almost there, thank you.
I just get "Divide by zero error encountered."
Go to Top of Page

mimuk
Starting Member

17 Posts

Posted - 12/05/2013 :  15:06:35  Show Profile  Reply with Quote
[Closed]*100.0/NULLIF([Quoted] + [Negotiating] + [Lost] + [Closed],0) AS Ratio

Seems to have fixed it... Many thanks, really appreciate it
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/06/2013 :  04:51:04  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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