CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Dragan Sekuloski
Dragan Sekuloski

Reputation: 897

Query SQL server - " is not contained in either an aggregate function or the GROUP BY clause"

When I try to execute this query:

SELECT 
    EventNo, Customer.CustNo, CustName,EstCost,
    SUM(EstCost) AS TotalEstCost, COUNT(*)
FROM 
    EventRequest 
INNER JOIN 
    Customer ON EventRequest.CustNo = Customer.CustNo
WHERE 
    Status = 'Approved'
GROUP BY 
    Customer.CustNo;

I get this error:

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

The table connection are here

enter image description here

Upvotes: 1

Views: 230

Answers (4)

Amit
Amit

Reputation: 46323

When you use a GROUP BY clause, you can only select grouped columns or aggregate non-grouped columns using an aggregation function (MAX, SUM...).

In your query, you're groupping by Customer.CustNo, but your trying to select EventNo (and others) which is causing the error.

You need to think about this column, as it makes no sense to select it. If you're groupping by customer, the result set will have a single row per customer (which makes a lot of sense if you want to aggregate some of this customer data, such as cost), but how would you like to ouput multiple EventNo values in a single row? You probably don't want it there at all. The same goes for EstCost (not the total).

As for the other fields that do make sense (such as CustName), you can add them to your GROUP BY clause and then you can select them directly.

Change your query to:

SELECT Customer.CustNo, CustName, SUM(EstCost) AS TotalEstCost, COUNT(*)
FROM EventRequest INNER JOIN Customer ON
  EventRequest.CustNo = Customer.CustNo
WHERE Status = 'Approved'
GROUP BY Customer.CustNo, CustName;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269803

I suspect that you want the total by customer and not by event. So, remove the extraneous columns in the SELECT that describe the event:

SELECT c.CustNo, c.CustName, SUM(EstCost) AS TotalEstCost, COUNT(*)
FROM EventRequest er INNER JOIN
     Customer c
     ON er.CustNo = c.CustN
 WHERE Status = 'Approved'
GROUP BY c.CustNo, c.CustName;

This will return one row per customers, along with the cost of the events and number of events.

Upvotes: 0

Matt
Matt

Reputation: 15071

You either need to GROUP BY all fields not contained in an aggregate function..

SELECT EventNo, c.CustNo, CustName, EstCost, SUM(EstCost) AS TotalEstCost, COUNT(*) 
FROM EventRequest er
INNER JOIN Customer c ON er.CustNo = c.CustNo 
WHERE Status = 'Approved' 
GROUP BY c.CustNo, EventNo, CustName, EstCost

Or select them using sub queries.

SELECT (SELECT ers.EventNo 
        FROM EventRequest ers 
        WHERE er.EventNo = ers.EventNo and ers.CustNo = c.CustNo) AS EventNo, 
        c.CustNo, 
       (SELECT cs.CustName 
        FROM Customer cs 
        WHERE c.CustName = cs.CustName AND c.CustNo = cs.CustNo) AS CustName, 
       (SELECT ers2.EstCost 
        FROM EventRequest ers2 
        WHERE er.EventNo = ers2.EventNo and ers2.CustNo = c.CustNo) AS EstCost, 
        SUM(EstCost) AS TotalEstCost, 
        COUNT(*) 
FROM EventRequest er
INNER JOIN Customer c ON er.CustNo = c.CustNo 
WHERE Status = 'Approved' 
GROUP BY c.CustNo

Upvotes: 4

The Reason
The Reason

Reputation: 7973

You have to add all fields from SELECT statement to your GROUP BY and after that you wil get expected result. Example:

SELECT EventNo, Customer.CustNo, CustName,EstCost, SUM(EstCost) AS TotalEstCost, COUNT(*) 
FROM EventRequest 
    INNER JOIN Customer ON EventRequest.CustNo = Customer.CustNo 
WHERE Status = 'Approved' 
GROUP BY EventNo, Customer.CustNo, CustName,EstCost;

Upvotes: 1

Related Questions