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
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