How SELECT column does not list in GROUP BY clause

Question

The query:

select year,sum(mark),sum(maxmark) 
from table1 
group by parameterno

yields an error message:

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

Answer 1

You can not select aggregates across a field if you don't include the field in the group by list.

If you want the totals per year you should write

SELECT year,sum(mark),sum(maxmark) 
FROM table1 
GROUP BY year

If you want the totals per parameterno it should be

SELECT parameterno,sum(mark),sum(maxmark) 
FROM table1 
GROUP BY parameterno

From the error message I guess you're using SQL Server so have a look at the documentation

Answer 2

It is not clear to me what you are asking but I believe that GROUP BY is one of the most misunderstood concepts in SQL, so I'll add this answer anyhow. It may or may not help with the understanding of the concept GROUP BY. Assume we have a table like:

CREATE TABLE T
( YEAR INT NOT NULL
, PARAMETERNO INT NOT NULL
, MARK INT NOT NULL
, PRIMARY KEY (YEAR, PARAMETERNO) );

INSERT INTO T ( YEAR, PARAMETERNO, MARK )
VALUES (2014,1,10),(2014,2,20),(2015,1,15),(2015,2,25);

What would:

SELECT YEAR, SUM(MARK)
FROM T
GROUP BY PARAMETERNO

mean? Grouping by PARAMETERNO means we have two groups

1    2014    10
     2015    20
2    2014    15
     2015    25

that we apply the aggregate function SUM on, but how does YEAR come in to play? This could mean:

     2014    30  -- 10+20 in group 1
     2015    30
     2014    40  -- 15+25 in group 2
     2015    40

but this is hardly a useful result? Another possibility is to randomly pick one row from each group, like:

     2014    30  -- 10+20 in group 1
     2015    40  -- 15+25 in group 2

The results are unpredictable and you may get different result for the same data/query.

SQL92 requires that all columns in the SELECT CLAUSE is part of the GROUP BY CLAUSE, so if we want:

SELECT YEAR, SUM(MARK)
FROM T

we would have to add at least YEAR to the GROUP BY:

SELECT YEAR, SUM(MARK)
FROM T
GROUP BY YEAR 

(it is possible to add other columns, not very common though). The result:

     2014    25 -- 10+15 in group 2014
     2015    45 -- 20+25 in group 2015

SQL99 loosens this restriction and requires that all columns in the SELECT clause is functionally determined by the GROUP BY clause (not the data per se, but the declared constraints). In a trivial case with one table it means that if the GROUP BY clause uniquely identifies a row we can add any column we want to the SELECT clause. Example:

CREATE TABLE T
( YEAR INT NOT NULL
, PARAMETERNO INT NOT NULL
, MARK INT NOT NULL
, PRIMARY KEY (YEAR) );

INSERT INTO T ( YEAR, PARAMETERNO, MARK )
VALUES (2014,1,10),(2015,2,20),(2016,1,15),(2017,2,25);

Now, beacause PARAMETERNO is functionally determined by YEAR a query like:

SELECT YEAR, PARAMETERNO, SUM(MARK)
FROM T
GROUP BY YEAR 

would be valid. AFAIK know recent versions of PostgreSQL and MySQL are the only ones that implements the SQL99 version of GROUP BY.

A trick for those DBMS that don't implement the SQL99 version is to apply an aggregate to those columns:

SELECT YEAR, MAX(PARAMETERNO), SUM(MARK)
FROM T
GROUP BY YEAR 

Since there is only one PARAMETERNO per YEAR, the result is the same.

MySQL historically allowed you to violate the SQL92 as well as the SQL99 GROUP BY rule, unless you specified ONLY_FULL_GROUP_BY in @@sql_mode. This may lead to un-deterministic results, and has caused much confusion in forums such as this one over the years. I personally always set @@sql_mode to ONLY_FULL_GROUP_BY when using older MySQL version than the latest (which I havent tried).