Math Sigma Formula With a Group By? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Math Sigma Formula With a Group By?

Author

Topic

tkepongo
Yak Posting Veteran

62 Posts

Posted - 2011-02-08 : 01:21:37

I'm new to SQL and having a tough time trying to get this formula:
GPA Avg= ?((points/credits)*count) / ?(count)

Pretty much I'm trying to get it so that it is grouped by CrseNo and that each CrseNo will have a calculated GPA average. Does anyone have any idea how to do this? I'm using SQL Server 2008. Thanks a lot!

Here's an image of the table:

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-02-08 : 01:37:14

Have you tried something like this?

select
CrseNo,
AVG(Points/Credits) as GPA_Avg
from Table
group by CrseNo

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-08 : 01:39:23

Try this

select crseno,(sum(points)*1.0/sum(credits))*count(*)/count(*) from table
group by crseno

Madhivanan

Failing to plan is Planning to fail

tkepongo
Yak Posting Veteran

62 Posts

Posted - 2011-02-08 : 02:29:59

quote:
Originally posted by madhivanan

Try this

select crseno,(sum(points)*1.0/sum(credits))*count(*)/count(*) from table
group by crseno

Madhivanan

Failing to plan is Planning to fail

Thanks for helping with this, but the results doesn't match with the excel calculations. I think it has to do with the "count". Did you mean the function count or the column name [count]?

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-08 : 02:40:04

can you provide the formula applied in the Excel for this calculation? Also, provide us the required output for the above data. This will help us manipulate the information as desired

tkepongo
Yak Posting Veteran

62 Posts

Posted - 2011-02-08 : 11:42:34

quote:
Originally posted by MIK_2008

can you provide the formula applied in the Excel for this calculation? Also, provide us the required output for the above data. This will help us manipulate the information as desired

Here's an image of a simple calculation:

Thanks!

tkepongo
Yak Posting Veteran

62 Posts

Posted - 2011-02-08 : 11:45:32

quote:
Originally posted by tkepongo

I'm new to SQL and having a tough time trying to get this formula:
GPA Avg= ?((points/credits)*count) / ?(count)

Pretty much I'm trying to get it so that it is grouped by CrseNo and that each CrseNo will have a calculated GPA average. Does anyone have any idea how to do this? I'm using SQL Server 2008. Thanks a lot!

Here's an image of the table:

Oh, I see what the problem is. My sigma character converted into a question mark. The formula is:

GPA Avg= sigma((points/credits)*count) / sigma(count)

I'm very sorry about that everyone

pdennis
Starting Member

1 Post

Posted - 2011-07-27 : 14:23:21

quote:
Originally posted by tkepongo

quote:
Originally posted by tkepongo

I'm new to SQL and having a tough time trying to get this formula:
GPA Avg= ?((points/credits)*count) / ?(count)

Pretty much I'm trying to get it so that it is grouped by CrseNo and that each CrseNo will have a calculated GPA average. Does anyone have any idea how to do this? I'm using SQL Server 2008. Thanks a lot!

Here's an image of the table:

Oh, I see what the problem is. My sigma character converted into a question mark. The formula is:

GPA Avg= sigma((points/credits)*count) / sigma(count)

I'm very sorry about that everyone

Hey, personally I like to use the WITH statement (common table expression) in cases like this, because it allows you to separate the work. If you're curious, just search Google for SQL WITH, and read the explanation on MSDN.

Try this out, it worked for me when I tested. The only thing you'll have to change is the word "Test". Change it to the name of your database table. Let me know if you still have trouble.

WITH IndividualGPA (CrseNo, [Ltr Grade], GPA, [Count])
AS
(
SELECT CrseNo, [Ltr Grade], ((Points/Credits)*[Count]) AS 'GPA', [Count]
FROM Test
)
SELECT CrseNo, (SUM(GPA)/SUM([Count]))
FROM IndividualGPA
GROUP BY CrseNo

--Paul

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources