Create query to average data & Total/sum data

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

Create query to average data & Total/sum data

Author

Topic

vbx
Starting Member

38 Posts

Posted - 2008-04-21 : 09:44:58

Hello,
I am very new to SQL and just getting to learn this stuff. To make this question easier I will scale down the fields dramatically.

I have about 8000 records close to 2000 records for the last 4 years
and I would like to create a query that will create a table on my SQL server. I need to bind the data based on two items the Year and the Name and average several records. However, one record needs it's own calculation.

Here are my field names:
[year] ***4 choices 2007, 2006, 2005, 2004***
[name]
[rush_no] ***integer***
[rush_net] ***integer***
[YPC] *** This field needs to be calculated by [rush_net] divided by [rush_no]***decimal***

I also need to create the same table that will "total/sum" the same records.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-21 : 11:27:36

You just need to create the table or you want it to populate as well? where do you get your source data from?

vbx
Starting Member

38 Posts

Posted - 2008-04-21 : 12:46:48

quote:
Originally posted by visakh16

You just need to create the table or you want it to populate as well? where do you get your source data from?

I can create the table and I could populate it. However, in the future when I get to running this in production. I would rather just call the query to refresh it. Otherwise I will be asking excel to calulate for long periods before loading it onto the database.

My source data comes from excel and gets uploaded into a master data table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-21 : 12:52:44

So what you require is a way to load data from excel to your table along with calculation of column values?

vbx
Starting Member

38 Posts

Posted - 2008-04-21 : 14:43:17

quote:
Originally posted by visakh16

So what you require is a way to load data from excel to your table along with calculation of column values?

No..Well yes that is an issue. But not for this thread.

See the below link.
[url]http://visualboxscore.com/boxscores/NCAA_football.aspx[/url]

As a hobby of mine I am creating this little website that allows users to filter out data based on called out criteria.

On the page linked above you can see a gridview table with a TOTAL in the footer in the bottom. I can use this method. However, because I need this same data in other areas I would like the data to already be calculated and ready. Currently this method calls the totals as the page is refreshed.

I am going to need this totals and averages for this page but will also use it for several others. Likewise, I will be looking up this same data for each team and will need to recover the data at those times aswell.

I could always call it in the asp.net web pages. But because I will be using them so often I would rather keep the webpages as clean as possible and let the server do the work.

understand ?? or are you now as confused as me?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-21 : 14:55:11

i will provide you with stub to work on:-

SELECT date,name,rush_no,rush_net,isnull(rush_net/nullif(rush_no,0),0)as YPC
FROM YourTable

UNION ALL

SELECT Year(Date),Name,SUM(rush_no),SUM(rush_net),isnull(SUM(rush_net)/nullif(SUM(rush_no),0),0)
FROM YourTable
GROUP BY Year(Date),Name

Hope this is what you're looking at. Likewise you can join your aggregated values along with detail and return them as single resultset toyour page.

vbx
Starting Member

38 Posts

Posted - 2008-04-22 : 08:40:26

visakh16,
Thank you for your help.

I am not real sure I understand all of it.

What exactly do you mean by "stub"?

This seems to generate the calculation of rush_net / rush_no.
But one thing that I need is that I only get one record for all names.

Example: Here is my raw data in table [cfb_boxscores]
Name-----rush_no-------rush_net-------YPC
A----------5-------------50-----------10.0
B----------4-------------30-----------7.5
C----------7-------------35-----------5.0
A----------6-------------65-----------10.8
B----------4-------------24-----------6.0
C----------8-------------32-----------4.0
A----------7-------------17-----------2.4
B----------9-------------18-----------2.0
C----------7-------------7------------1.0
A----------3-------------24-----------8.0
B----------10------------33-----------3.3
C----------11------------25-----------2.3

I want to get the average of column. But we can't just average the YPC column because it will not be a true average.

Therefore, We need to sum each column for each name.
rush_no / rush_net
A=21 / 156 = 7.4 (if I just average the YPC = 7.8)
B=27 / 105 = 3.9 (if I just average the YPC = 4.7)
C=33 / 99 = 3.0 (if I just average the YPC = 3.1)

By simply averaging the columns and grouping by the [name] will give the wrong data. I actually need to sum the first two and re calculate for the YPC.

I need my end result table to look like this
Name-----rush_no-------rush_net-------YPC
A---------5.3-----------39------------7.4
B---------6.8-----------26.3----------3.9
C---------8.3-----------24.8----------3.0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-22 : 08:59:51

By stub i meant just a draft query. I can provide you only a draft one as i dont know your fields and their values. DOnt take AVG(YPC). But calculate it as SUM(rush_no)/Sum(rush_net) as posted earlier.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources