Sum then Divide

Question

Sum then Divide

ccmret

Right there with Babe

Points: 759
More actions
April 1, 2013 at 10:50 am

#291616

Is there any easier way to do this sum/divide without having to put results of sum in a temp table first? Trying to do it all in one query give me:
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
SELECT Distinct LEFT(CONVERT(DateTime, IndexDate , 110), 11)AS IndexDate,
COUNT(*) AS No_Records,
Round(AVG(Test1),2) As Test1_AVG,
Round(AVG(Test2),2) AS Test2_AVG,
SUM(t_complete/COUNT (*)) As Percent_Complete
FROM TestScores.dbo.Analysis
Group by IndexDate

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

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

Sean Lange SSC Guru Points: 286602 More actions · Answer 1

ccmret (4/1/2013)
Is there any easier way to do this sum/divide without having to put results of sum in a temp table first? Trying to do it all in one query give me:
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
SELECT Distinct LEFT(CONVERT(DateTime, IndexDate , 110), 11)AS IndexDate,
COUNT(*) AS No_Records,
Round(AVG(Test1),2) As Test1_AVG,
Round(AVG(Test2),2) AS Test2_AVG,
SUM(t_complete/COUNT (*)) As Percent_Complete
FROM TestScores.dbo.Analysis
Group by IndexDate

You can't do it like that because you have an aggregate within an aggregate.

Try this.

SUM(t_complete)/COUNT (*) As Percent_Complete

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ccmret Right there with Babe Points: 759 More actions · Answer 2

The query successfully completed with your suggestion. Howerver, it returned a zero.

Lynn Pettis SSC Guru Points: 442467 More actions Follow · Answer 3

ccmret (4/1/2013)
The query successfully completed with your suggestion. Howerver, it returned a zero.

Integer math, try:

SUM(t_complete)/(COUNT (*) * 1.0) As Percent_Complete

ccmret Right there with Babe Points: 759 More actions · Answer 4

Still provides 0's. The numbers that I am trying to divide are:

t_complete = 176420

Count = 5265365

Format is tinyint.

Thoughts?

Lynn Pettis SSC Guru Points: 442467 More actions Follow · Answer 5

ccmret (4/1/2013)
Still provides 0's. The numbers that I am trying to divide are:
t_complete = 176420
Count = 5265365
Format is tinyint.
Thoughts?

Please explain what you mean by format is tinyint?

Sean Lange SSC Guru Points: 286602 More actions · Answer 6

Lynn Pettis (4/1/2013)
ccmret (4/1/2013)
Still provides 0's. The numbers that I am trying to divide are:
t_complete = 176420
Count = 5265365
Format is tinyint.
Thoughts?
Please explain what you mean by format is tinyint?

I have a feeling this is being in a tinyint variable/column. Either way this pointless. Not only does tinyint not have anywhere near enough storage it will only hold whole numbers between 0 and 255. The values you posted would be less than 1 so a tinyint will have 0.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lynn Pettis SSC Guru Points: 442467 More actions Follow · Answer 7

Based on what you posted:

declare @t_complete int = 176420,

@myCount int = 5265365;

select @t_complete / @mycount, @t_complete / (@mycount * 1.0)

Results:

IntDivision DecimalDivision

0 0.03350574936400

ccmret Right there with Babe Points: 759 More actions · Answer 8

That worked.....I ended up doing this:

Declare @Denominator int, @NumeratorT int, @NumeratorT1 int

SET @Denominator = (SELECT COUNT(*) FROM TestScores.dbo.Analysis)

SET @Numerator = (SELECT SUM(t_complete) TestScores.dbo.Analysis)

SET @Numerator = (SELECT SUM(t1_complete) TestScores.dbo.Analysis)

SELECT Distinct LEFT(CONVERT(DateTime, IndexDate , 110), 11)AS IndexDate,

COUNT(*) AS No_Records,

Round(AVG(Test1),2) As Test1_AVG,

Round(AVG(Test2),2) AS Test2_AVG,

SUM(t_complete/COUNT (*)) As Percent_Complete

SUM(t_complete) As Initial_Completed,

CONVERT(Decimal(5,3),@NumeratorT/(@Denominator* .01)) As PercentComplete,

SUM(t1_completed)AS Repeats_Completed,

CONVERT(Decimal(5,3),@NumeratorT1/(@Denominator*.01)) AS Repeats_Percent,

FROM TestScores.dbo.Analysis

Group by IndexDate

Thoughts?

Sum then Divide

Cookies on SQLServerCentral