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:
Using an aggregate
SELECT A, COUNT(B) AS T1, SUM(B) AS B
FROM T2
GROUP BY Awould return:
A T1 B
1 2 92
2 3 68Adding the column to the
GROUP BY
listSELECT A, COUNT(B) AS T1, B
FROM T2
GROUP BY A, Bwould 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
How to Convert Timestamp with Milliseconds to Date in Oracle
Refactor Foreign Key to Fields
How to Unpivot a Table in Postgresql
Oracle Autoincrement with Sequence and Trigger Is Not Working Correctly
SQL Query for Today's Date Minus Two Months
SQL Query Where Field Does Not Contain $X
SQL Statement Using Where Clause with Multiple Values
"Missing Right Parenthesis": on Delete Set Null on Update Cascade (Sql/Oracle)
Percentiles from Histogram Data
What Does "&" Means in This SQL Where Clause
SQL Update All Values in a Field with Appended String Concat Not Working
Move SQL Server 2008 Database Files to a New Folder Location
Check If Entry in Table a Exists in Table B
How to Understand How Value Converted to Date Format Implicitly
It's Possible to Create a Rule in Preceding Rows in Sum
Using of Rownum Function with ">" Sign in Oracle
Running a Stored Procedure with Nodejs and Mssql Package Error
Split String into Table Given Row Delimiter and Column Delimiter in SQL Server