sql-server-2005

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


I don't understand why the column 'OrderInfo.DateSold' is having an error for the following stored procedure. This is in SQL Server 2005.

SELECT DateSold,
       Rep.FirstName,
       Company,
       Contact,
       Phone,
       OPD.TotalAmount                              AS Sold,
       SUM(OPD.PaymentAmount)                       AS Collected,
       ( OPD.TotalAmount - SUM(OPD.PaymentAmount) ) AS Balance
FROM   OrderInfo
       INNER JOIN Order_PaymentDetails OPD
         ON OrderInfo.OrderID = OPD.OrderID
       INNER JOIN SalesRep_Info Rep
         ON OrderInfo.RepID = Rep.RepID
WHERE  OrderInfo.CommuniTeeID = @CommuniTeeID
GROUP  BY OrderInfo.Company 

Solution

  • This is because if you want to see in resulting set the column "Datesold" and so on, you need to group by this fields too:

    SELECT 
        DateSold,
        Rep.FirstName,
        Company,
        Contact,
        Phone,
        OPD.TotalAmount AS Sold,
        SUM(OPD.PaymentAmount) AS Collected,
        (OPD.TotalAmount - SUM(OPD.PaymentAmount)) AS Balance 
    FROM   
        OrderInfo
    INNER JOIN 
        Order_PaymentDetails OPD ON OrderInfo.OrderID = OPD.OrderID
    INNER JOIN 
        SalesRep_Info Rep ON OrderInfo.RepID = Rep.RepID 
    WHERE  
        OrderInfo.CommuniTeeID = @CommuniTeeID
    GROUP BY 
        OrderInfo.Company, DateSold, Rep.FirstName, Contact,
        Phone, OPD.TotalAmount