[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Need help with T-SQL script: How to average a field across records

Posted on 2011-10-05
Medium Priority
Last Modified: 2012-05-12
I'm in need of a purely t-sql script that will average a single integer field across potentially tens of thousands of records. Note the following script:

select stitches from meistergram WHERE rundate BETWEEN '2011-01-01' AND '2011-12-31'

This script identifies the table (meistergram), the integer field (stitches) and qualifies the records to be averaged by a range of dates. Please use this as a basis for adding the additional t-sql required to return just the average of the stitches field. Thanks.
Question by:tcianflone
LVL 27

Expert Comment

by:Shaun Kline
ID: 36917268
Do you want an average across the entire record set? (select avg(stitches) from meistergram ..
Do you want an average grouped? More information is needed.
LVL 61

Accepted Solution

HainKurt earned 2000 total points
ID: 36917289
looks like he is asking for a simple avg

select avg(stitches) from meistergram WHERE rundate BETWEEN '2011-01-01' AND '2011-12-31'

Author Comment

ID: 36917298
The requirement is to get an average of stitches across the entire set of records returned within the date range. As for grouping, I'm not familiar with how I might group the results.
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

LVL 10

Expert Comment

ID: 36917402
select AVG(stitches) as 'Avarage' from meistergram WHERE rundate BETWEEN '2011-01-01' AND '2011-12-31' GROUP BY rundate
LVL 10

Expert Comment

ID: 36917412
select AVG(stitches) ,rundate from meistergram WHERE rundate BETWEEN '2011-01-01' AND '2011-12-31' GROUP BY rundate,stitches
LVL 27

Expert Comment

by:Shaun Kline
ID: 36917463
Please be aware that if rundate is a datetime field includes a time component, grouping by rundate will most likely not give you the result you are looking for:

SELECT CONVERT(varchar(10), rundate, 120) RunDate, AVG(Stitches) FROM meistergram WHERE rundate BETWEEN '2011-01-01' AND '2011-12-31 GROUP BY CONVERT(varchar(10), rundate, 120)

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question