user3230654 user3230654 - 3 years ago 72
SQL Question

SQL Select statement not returning desired columns

I have a table (sample) with 10 rows, that has columns of place_name, 5 days worth of data (day1, day2,...,day5) for each location, and a sum column (TotalSum) of the 5 days. TotalSum at the beginning is full of null values.

place_name | day1 | day2 | day3 | day4 | day5 | TotalSum
PlaceA | 1 | 1 | 1 | 1 | 1 | NULL

I have an update statement that will sum up the five days, and put them into the TotalSum column for each of the 10 locations.

update sample set sample.TotalSum = day1+day2+day3+day4+day5;

I want to then select the location (place_name) with the maximum TotalSum value. I have been trying it with the following line:

SELECT place_name, MAX(TotalSum) AS MAXsum FROM sample;

The code does not work and gives an error saying,

"Column 'sample.place_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

If I execute the code below, then all I get is the MAXsum value, nothing else.

SELECT MAX(TotalSum) AS MAXsum FROM sample;

Working in Microsoft SQL Server Management Studio, 2014.

Answer Source

the reason you are getting this message is you are trying to aggregate the column TotalSum but you also have a non agregated column place_name. but you are not defining the group which the column can be aggregated by in a GROUP BY clause

try this:

SELECT place_name, MAX(TotalSum) AS MAXsum 
FROM sample 
GROUP BY place_name
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download