SQL Query Group By - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

SQL Query Group By

Author

Topic

mimuk
Starting Member

19 Posts

Posted - 2013-12-05 : 13:10:53

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

52326 Posts

Posted - 2013-12-05 : 13:28:43

[code]
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
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mimuk
Starting Member

19 Posts

Posted - 2013-12-05 : 14:53:52

HI visakh16, thats almost there, thank you.
I just get "Divide by zero error encountered."

mimuk
Starting Member

19 Posts

Posted - 2013-12-05 : 15:06:35

[Closed]*100.0/NULLIF([Quoted] + [Negotiating] + [Lost] + [Closed],0) AS Ratio

Seems to have fixed it... Many thanks, really appreciate it

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-06 : 04:51:04

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources