asked
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
@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.
@Oleg Yes, the sites column is numeric data (numeric 7,0). I rounded down to be more succinct.
@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
The select statement in the comment is pretty far off, so it might make sense to visit the [SELECT (Transact-SQL)][1] 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 [1]:
https://docs.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql
@Oleg, Getting Syntax errors about the sites and employment. Any ideas?
@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.
@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.
@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.
18 People are following this question.
