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

x
Solved

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

Posted on 2011-10-05
Medium Priority
290 Views
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.
0
Question by:tcianflone

LVL 27

Expert Comment

ID: 36917268
Do you want an average across the entire record set? (select avg(stitches) from meistergram ..
0

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'
0

LVL 1

Author Comment

ID: 36917298
Shaun,
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.
0

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
0

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
0

LVL 27

Expert Comment

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)
0

## Featured Post

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
Course of the Month20 days, 2 hours left to enroll