Calculating Percentage using a CTE Or with a COUNT(*) OVER Clause

Question

Post reply

Calculating Percentage using a CTE Or with a COUNT(*) OVER Clause

Welsh Corgi

SSC Guru

Points: 116520
More actions
November 22, 2011 at 12:50 pm

#247377

How can I get the percentage Quotes that became a Sale using a CTE?
SELECT TOP 5 [Marketing Rep], MarketingTerritoryName,OrgTypeDescription, SUM(Quote) AS Quote, SUM(Sale) AS Sale
FROM QuoteActivity
GROUP BY [Marketing Rep], MarketingTerritoryName,OrgTypeDescription
Marketing RepMarketingTerritoryNameAgencyTypeDescriptionQuoteSale
Shallow HalCentral Market Solutions Group158 3
Shallow HalSoutheast Independent 36 4
Shallow HalSouthwest EggHead 613140
Shallow HalSouthwest Select Independents 35 2
Shallow HalSouthwest Fiesta 116 12
Any help would be greatly appreciated
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

Going to need to back this up a bit. How do you know if something became a sale from the raw data?

Or are you looking for an in-line percentage like SUM(SAle)/SUM(Quote) AS PercentageConverted?

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

Craig,

I'm looking for the SUM(Sale)/SUM(Quote) WHERE Sale could be zero.

Thanks!

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

drew.allen SSC Guru Points: 77014 More actions · Answer 3

100.00 * Sum(Sale)/NULLIF(Sum(Quote), 0)

This makes sure that you are not performing integer division and also produces a null result when Sum(Quote) is zero.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

Awesome.

I'm trying to get the result rounded and Converted as follows:

No. of QuotesNo. Sold% Sold

140 3 2%

36 4 11%

599 135 23%

35 2 6%

18,409 4,364 24%

11 0 0%

328 52 16%

959 192 20%

347 41 12%

106 9 8%

20,794 4,795 23%

20,970 4,802 23%

2 0 0%

76 4 5%

78 4 5%

1 1100%

25 1 4%

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

The following does not right justify the percentage:

CAST(CONVERT(Decimal(9,2),ROUND(100.00 * Sum(Sale)/NULLIF(Sum(Quote), 0) ,2)) AS VARCHAR(6)) + '%'

NoSales

1.90%

11.11%

22.80%

5.71%

10.34%

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

drew.allen SSC Guru Points: 77014 More actions · Answer 6

Welsh Corgi (11/22/2011)
The following does not right justify the percentage:

Justification is a presentation issue and should be handled by the presentation layer not in T-SQL.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

Thanks for the formula.:-)

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

drew.allen (11/22/2011)
Welsh Corgi (11/22/2011)
The following does not right justify the percentage:
Justification is a presentation issue and should be handled by the presentation layer not in T-SQL.
Drew

No hate mail please.:hehe:

I thought that the following was interesting as listed in Jeff Moden's article that you posted in another thread: http://www.sqlservercentral.com/Forums/Topic1210948-338-1.aspx#bm1211028

http://www.sqlservercentral.com/articles/T-SQL/63681/

For most applications, that's good enough. If it's supposed to represent the final output,

we might want to make it a little prettier.

The STR function inherently right justifies, so we can use that to make the output a little prettier.

Please, no hate mail here! I'll be one of the first that formatting of this nature is supposed to be done in the GUI!

--===== We can use the STR function to right justify data and make it prettier.

-- Note that this should really be done by the GUI or Reporting Tool and

-- not in T-SQL

SELECT Year, STR(SUM(CASE WHEN Quarter = 1 THEN Amount

ELSE 0 END),5,1) AS [1st Qtr],

STR(SUM(CASE WHEN Quarter = 2 THEN Amount

ELSE 0 END),5,1) AS [2nd Qtr],

STR(SUM(CASE WHEN Quarter = 3 THEN Amount

ELSE 0 END),5,1) AS [3rd Qtr],

STR(SUM(CASE WHEN Quarter = 4 THEN Amount ELSE 0 END),5,1) AS [4th Qtr],

STR(SUM(Amount),5,1) AS Total

FROM #SomeTable1 GROUP BY Year

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/