SUM then Divide - 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 2005 Forums

Transact-SQL (2005)

SUM then Divide

Author

Topic

meemo
Starting Member

9 Posts

Posted - 2011-12-05 : 11:57:36

I would like to sum up all the scores and all the points in one table then do an average of that total.

So if I have a table ABC like this:


ID Score Points
1  10    15
1  20    25 
1  30    35
1  40    45
1  50    55
2  11    61
2  12    71
2  13    81

I tried to do:


select sum(Score) as ts, sum(Points) as tp, sum(Score) / sum(Points) as [Average] 
from ABC where ID = 1

Which means it should:
10+20+30+40+50 = 150
15+25+35+45+55 = 175

Then I need to 150/175 = 0.85 (which I also need to show in the column).

So then it will display a single row:


ID Score Points Average
1  150   175    0.85

Should this be done in a script or can I do this directly in SQL (which I would prefer)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-05 : 12:01:43

this will do it in one shot for all ID values


select ID,sum(Score) as ts, sum(Points) as tp, sum(Score)*1.0 / sum(Points) as [Average] 
from ABC 
group by ID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-05 : 12:02:33

I would say, do it in T-SQL; in fact, this is the kind of thing that SQL excels at. The only suggestion I would have is to do something to convert the ratio to float if the data types of the two columns are integers. For example:

select sum(Score) as ts, sum(Points) as tp, sum(Score)*1.0 / sum(Points) as [Average] 
from ABC where ID = 1

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-12-05 : 12:02:50

select id, sum(score), sum(points), convert(decimal(4,2),1.0*sum(score)/sum(points))
from tbl
group by id

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

meemo
Starting Member

9 Posts

Posted - 2011-12-05 : 12:41:23

Ok, thanks, that fixes it.

However, this is a subset of a bigger query I need to run. So please help.

My final report needs to look like:


Store#  Date       Average
1       03-Jan-11  85%
1       04-Jul-11  75%
2       01-Feb-11  60%
2       18-Jun-11  54%
2       05-Nov-11  78%

I get this data from these tables:

Store -> gets the store number
Date -> gets the date
Score -> gets the average

This is what each table looks like...

------------------------

Table STORE:


StoreID StoreName      StoreNumber
1       John's Store   234
2       Peter's Store  567
3       Sandra's Store 789

------------------------

Table DATE:


AID StoreID DateCreated
1   1       04-Jul-11
2   2       01-Feb-11     
3   1       03-Jan-11
4   3       etc.
5   4
6   2
7   5

------------------------

Table SCORE:


AID Score Points
1   10    15
1   20    25 
1   30    35
1   40    45
1   50    55
2   11    21
2   12    31
2   13    41

------------------------

The AID column in SCORE and DATE is linked. In the DATE table, AID exist once. In the SCORE table, there is multiple rows.

The StoreID in the DATE table is linked with StoreID in the STORE table.

So, the logic is:

1. Get the average of scores for each AID.
- for AID 1: (10+20+30+40+50) / (15+25+35+45+55) = 0.85

2. For each store, fetch the AID average and DateCreated columns.

3. So the output for this case would be:


Store#  Date       Average
1       03-Jan-11  85%

Then again for this same store, different date:


1       04-Jul-11  75%

Hopefully there is an sql way :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-05 : 13:01:11

[code]
select st.StoreID AS [Store#],
d.DateCreated AS [Date],
s.[Average]
from STORE st
inner join DATE d
on d.StoreID = st.StoreID
inner join
(
select AID,sum(Score)*1.0 / sum(Points) as [Average]
from SCORE
group by AID
)s
on s.AID = d.AID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

meemo
Starting Member

9 Posts

Posted - 2011-12-05 : 13:32:51

Thanks a lot visakh16! That helped.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-05 : 23:41:12

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources