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

Question

I have three tables

Table 1:

tblCustomer
CustomerID CustomerName
1  ABC Bank
2 Chase Bank

Table 2:

tblOrderType
OrderTypeID OrderTypeName
1   Assignment
2   LienRelease

Table 3:

tblOrder
OrderID CustomerID  OrderTypeID LoanNumber
1   1   1   45584565
2   1   1   45566856
3   1   1   45565584
4   1   1   45588545

I am trying to return CustomerID, CustomerName, OrderTypeName and OrderCount in CustomerName order but when using the following query:

SELECT tblOrder.CustomerID,CustomerName,OrderTypeName,COUNT(tblOrder.CustomerID)  FROM tblOrder
INNER JOIN tblCustomer ON tblOrder.CustomerID=tblCustomer.CustomerID
INNER JOIN tblOrderType ON tblOrder.OrderTypeID=tblOrderType.OrderTypeID
GROUP BY tblOrder.CustomerID

I am getting the error:

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

I dont know what I am doing wrong.

You have apostrophes in your query, f.e. at `'SELECT tblOrder.'CustomerID,CustomerName,`, are they really there? — Tim Schmelter, Apr 27 '15 at 13:06
You need to put CustomerName and OrderTypeName in the Group By statement — Andrei Hirsu, Apr 27 '15 at 13:08
possible duplicate of [What does the "invalid, not contained in either an aggregate function" message mean?](http://stackoverflow.com/questions/18258704/what-does-the-invalid-not-contained-in-either-an-aggregate-function-message-m) — Tanner, Apr 29 '15 at 13:33

score 2 · Answer 1 · answered Apr 27 '15 at 13:07

2

Your SELECT and GROUP BY statements should have same fields. In your case

SELECT tblOrder.CustomerID,CustomerName,OrderTypeName

answered Apr 27 '15 at 13:07

Evaldas Buinauskas

13,739
11
55
107

Yes. The only field that doesn't need to be in group by clause is the count() – Márcio Gonzalez Apr 27 '15 at 13:08

paparazzo · Answer 2 · 2015-04-27T13:14:46.860

Something like this

SELECT tblOrder.CustomerID, CustomerName, OrderTypeName, COUNT(*)
FROM tblOrder
INNER JOIN tblCustomer ON tblOrder.CustomerID=tblCustomer.CustomerID
INNER JOIN tblOrderType ON tblOrder.OrderTypeID=tblOrderType.OrderTypeID
GROUP BY tblOrder.CustomerID, CustomerName, OrderTypeName

or this

SELECT tblOrder.CustomerID, CustomerName, COUNT(*)  
FROM tblOrder
INNER JOIN tblCustomer ON tblOrder.CustomerID = tblCustomer.CustomerID
GROUP BY tblOrder.CustomerID, CustomerName

score 0 · Answer 3 · answered Apr 27 '15 at 13:09

The answer is exactly what the error message is telling you.

You cannot select columns which aren't in the group by clause OR aggregated by somehow.

You also haven't included the actual order by clause.

SELECT 
    tblOrder.CustomerID,
    tblCustomer.CustomerName,
    tblOrderType.OrderTypeName,
    COUNT(tblOrder.CustomerID)  
FROM 
    tblOrder
    INNER JOIN tblCustomer ON tblOrder.CustomerID=tblCustomer.CustomerID
    INNER JOIN tblOrderType ON tblOrder.OrderTypeID=tblOrderType.OrderTypeID
GROUP BY 
    tblOrder.CustomerID,
    tblCustomer.CustomerName,
    tblOrderType.OrderTypeName,
ORDER BY
    tblCustomer .CustomerName ASC

score 0 · Answer 4 · answered Apr 27 '15 at 13:10

If you use GROUP BY in SQl-Server you have to aggregate all selected columns(f.e. with Min, Max, Count etc) or you have to include them in the GROUP BY. That makes sense because it's not clear which row's value you want to take for CustomerName and OrderTypeName, there could be multiple for each id-group.

If that's not the case include it in the Group by:

SELECT tblOrder.CustomerID,
       CustomerName,
       OrderTypeName,
       COUNT(tblOrder.CustomerID) As CountCustomerID
FROM tblOrder
INNER JOIN tblCustomer ON tblOrder.CustomerID=tblCustomer.CustomerID
INNER JOIN tblOrderType ON tblOrder.OrderTypeID=tblOrderType.OrderTypeID
GROUP BY tblOrder.CustomerID, CustomerName, OrderTypeName

Another way in SQL-Server(>=2005) is using a CTE(or subquery) + ROW_NUMBER, then you can select all fields:

WITH CTE AS
(

    SELECT tblOrder.CustomerID,
           CustomerName,
           OrderTypeName,
           Count(*) OVER (PARTITION BY tblOrder.CustomerID, CustomerName, OrderTypeName),
           RN = ROW_NUMBER() OVER (PARTITION BY tblOrder.CustomerID, CustomerName, OrderTypeName 
                                   ORDER BY CustomerID, CustomerName)
    FROM tblOrder
    INNER JOIN tblCustomer ON tblOrder.CustomerID=tblCustomer.CustomerID
    INNER JOIN tblOrderType ON tblOrder.OrderTypeID=tblOrderType.OrderTypeID
)
SELECT * FROM CTE
WHERE RN = 1

score 0 · Answer 5 · answered Apr 27 '15 at 13:14

You need to aggregate the columns that are not included in your GROUP BY statement:

SELECT A.CUSTOMERID, MAX(B.CUSTOMERNAME), MAX(C.ORDERTYPENAME), COUNT(A.CUSTOMERID)  
FROM TBLORDER AS A
INNER JOIN TBLCUSTOMER  AS B
ON A.CUSTOMERID=B.CUSTOMERID
INNER JOIN TBLORDERTYPE AS C
ON A.ORDERTYPEID=C.ORDERTYPEID
GROUP BY A.CUSTOMERID

Or you can group by the same columns in your SELECT.

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

5 Answers5