x

Averaging selectively in SQL

This is a bit convoluted but I will try to make it not so. Below is a snippet of the data and the intended result below it. For every combination of state, area, indcode, and ownership I want to calculate an average. My knowledge of AVG in SQL would average the entire column not just specific values. How does one do this?

 state   areatype   area   period   indcode   ownership    sites    employment
  32        05     000001     01    102800        12          25         256
  32        05     000001     01    102900        13           6          26
  32        05     000003     01    102800        12         774        1874
   .
   .   
  32        05     000001     02    102800        12          27         239
  32        05     000001     03    102800        12          28         241
  32        05     000001     04    102800        12          29         248

Intended result

  32        05     000001     00    102800        12          27         246
more ▼

asked Oct 10 at 11:33 PM in Default

avatar image

nevada1978
21 2

group by the columns in which you have an interest. e.g., select... from... group by state, area, indcode, ownership

6 days ago KenJ

@nevada1978 The average of sites for the first combination in the sample data (32, 05, 000001, 102800, 12) is 27.25 not 27, so it does not match the info displayed in the intended results. This probably means that the sites column is integer data type so the average returns integer as well thus removing the decimal part. Please confirm that this is the case. Also, @KenJ suggestion includes, or at least strongly hints the answer, but please reply if you still need help with the query.

6 days ago Oleg

@Oleg Yes, the sites column is numeric data (numeric 7,0). I rounded down to be more succinct.

6 days ago nevada1978

@KenJ So, would it look like such?

  select all fields  from dbo.tablename  group by state, area, indcode, ownership
  select avg (sites) from dbo.tablename
  select avg (employment) from dbo.tablename
6 days ago nevada1978
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

The select statement in the comment is pretty far off, so it might make sense to visit the SELECT (Transact-SQL) page on MSDN site and study it. After all, select is the most important feature of T-SQL. Here is the statement which should produce desired results:

 select
     [state], areatype, area, '00' [period], indcode, ownership, 
     avg(sites) sites, avg(employment) employment
     from dbo.tablename
     group by [state], areatype, area, indcode, ownership;

Generally speaking, the practice of using T-SQL keywords as column names is ill advised, but I know it happens all the time. If the keyword must be used for column name then any references to it from the script should be wrapped into square brackets.

Hope this helps.

Oleg

more ▼

answered Oct 11 at 05:14 PM

avatar image

Oleg
18.6k 3 7 28

@Oleg,

Getting Syntax errors about the sites and employment. Any ideas?

4 days ago nevada1978

@nevada1978 What is the text of the error message? Does your table have these columns? Please let me know. I mocked up some sample data and then ran the script. It works as expected. I hope that the database in question is SQL Server database. The same script will work in Oracle, MySQL and Sybase though. In the past, there were problems with copying the code from the site as doing so would include the line numbers, but this problem has been resolved long time ago.

4 days ago Oleg

@Oleg It was incorrect syntax near Sites. Not sure what was the issue but instead of copying and pasting, I transcribed it from the site and it worked. Thanks for your help. I think it needs some tweaking but need to really analyze it.

4 days ago nevada1978

@nevada1978

I believe the problem may be that when you simply copy and paste, the bloody line numbers you see on the screen also get copied. You have to manually remove them.

2 days ago Jeff Moden
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x159
x5

asked: 6 days ago

Seen: 50 times

Last Updated: 2 days ago

Copyright 2017 Redgate Software. Privacy Policy