Error: Invalid Expression in the Select List (Not Contained in Either an Aggregate Function or the Group by Clause)

Error: Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)

The usage of GROUP BY makes the engine group the records for you. To do grouping, you have to give advice to the RDBMS for each column, what it should do.

  • Group it? -> Add column to GROUP BY-Clause
  • Not group it? -> ok, what else?

    • ignore the column? remove it from your select-clause
    • Sum it? -> use SUM(mycol)
    • other aggregation functions can be found in the documentation

Additionally: In your case you try to group by EPS_ID, which is unique in each row. So a grouping by that column will return all rows, because there is nothing to group by. To group records, they have to have the same value.

Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Suppose I have the following table T:

a   b
--------
1 abc
1 def
1 ghi
2 jkl
2 mno
2 pqr

And I do the following query:

SELECT a, b
FROM T
GROUP BY a

The output should have two rows, one row where a=1 and a second row where a=2.

But what should the value of b show on each of these two rows? There are three possibilities in each case, and nothing in the query makes it clear which value to choose for b in each group. It's ambiguous.

This demonstrates the single-value rule, which prohibits the undefined results you get when you run a GROUP BY query, and you include any columns in the select-list that are neither part of the grouping criteria, nor appear in aggregate functions (SUM, MIN, MAX, etc.).

Fixing it might look like this:

SELECT a, MAX(b) AS x
FROM T
GROUP BY a

Now it's clear that you want the following result:

a   x
--------
1 ghi
2 pqr

Invalid expression in the select list when using group by

GROUP BY is probably one of the concepts that confuse people most when starting out with SQL, so you are far from alone getting into trouble. Lets for simplicity assume the following table:

CREATE TABLE T
( a int not null
, b int not null
);

INSERT INTO T (a,b) VALUES (1,1), (1,2);

What would the following mean?

SELECT a, b
FROM T
GROUP BY a

In the group determined by a we have two different values for b, namely 1 and 2. Which row should be returned:

(1,1) or (1,2)?

So the query does not uniquely define a result. Older versions of MySQL (with default setting) would by default accept the query, and randomly pick one of these rows. All other DBMS that I know of does not accept the query. You can either add all columns not part of an aggregate, to your GROUP BY like saravanatn does:

SELECT a, b
FROM T
GROUP BY a, b

which is the same as:

SELECT DISTINCT a, b
FROM T

or add an aggregate function for b as Gordon Linoff does in his answer:

SELECT a, SUM(b)
FROM T
GROUP BY a

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

Put in other words, this error is telling you that SQL Server does not know which B to select from the group.

Either you want to select one specific value (e.g. the MIN, SUM, or AVG) in which case you would use the appropriate aggregate function, or you want to select every value as a new row (i.e. including B in the GROUP BY field list).


Consider the following data:


ID A B
1 1 13
1 1 79
1 2 13
1 2 13
1 2 42

The query

SELECT A, COUNT(B) AS T1 
FROM T2
GROUP BY A

would return:


A T1
1 2
2 3

which is all well and good.

However consider the following (illegal) query, which would produce this error:

SELECT A, COUNT(B) AS T1, B 
FROM T2
GROUP BY A

And its returned data set illustrating the problem:


A T1 B
1 2 13? 79? Both 13 and 79 as separate rows? (13+79=92)? ...?
2 3 13? 42? ...?

However, the following two queries make this clear, and will not cause the error:

  1. Using an aggregate

    SELECT A, COUNT(B) AS T1, SUM(B) AS B
    FROM T2
    GROUP BY A

    would return:


    A T1 B
    1 2 92
    2 3 68
  2. Adding the column to the GROUP BY list

    SELECT A, COUNT(B) AS T1, B
    FROM T2
    GROUP BY A, B

    would return:


    A T1 B
    1 1 13
    1 1 79
    2 2 13
    2 1 42

TSQL / SQL - Error: Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

MERGE INTO [bo_marketing_capability_dev].[dbo].[Master_Users_SVOC_test_vt] AS m 
USING (
SELECT
m.subscriber_key,
m.email_address,
w.[First_Name] AS first_name,
w.[Last_Name] AS last_name,
, MAX (w.DateAdded) AS wayin_DateAdded
FROM Wayin_Integration_SFMC_AD w
INNER JOIN Master_Users_SVOC_test_vt m
ON w.Email = m.email_address
GROUP BY m.subscriber_key, m.email_address, w.[First_Name], w.[Last_Name]
) AS SRC
ON ([SRC].[email_address] = [m].[email_address])

Try above code.
GROUP BY should have all non aggregate columns.

Column is invalid in the select list due to it is not contained in either an aggregate function or the GROUP BY clause

Because in SELECT you have used datepart(YEAR, OrderDate) searching for YEAR part, and in group by you have datepart(Y, OrderDate) - Y is not short from YEAR but actually stand for DayOfYear

https://learn.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql

Short for Year is YY

Query would work as :

select DATEPART(Q, OrderDate) Q , datepart(YEAR, OrderDate) Y, count(*) count 
from Orders
group by DATEPART(Q, OrderDate), datepart(YY, OrderDate);

You can see in this DEMO - using YY will give 2017 for today, using Y will give 230 - as it's 230th day in this year

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

The column must appear in the FROM clause of the SELECT statement, but is not required to appear in the SELECT list.
You can know more in the documentation about the valid expression in the clause GROUP BY :

SELECT       c.customerid, 
c.customername,
totalamt,
.
.
.
FROM consumermst_lko AS c
RIGHT OUTER JOIN invoicedetails AS invoice
ON c.customerid = invoice.customerid
WHERE c.customerid='LKO00028153'
GROUP BY c.customerid
, c.customername,...


Related Topics



Leave a reply



Submit