Linking three tables and getting an error about not being contained in 'aggregate function or the GROUP BY caluse'

Transact-SQL
1 
SELECT Customers.Account, Customers.Deptname, Renewal.Id, Customers.ID, Renewal.Startdate, Renewal.enddate, Renewal.Status, Renewal.PrintedOn, COUNT(InvoiceLineItems.invID) FROM Customers INNER JOIN Renewal ON Customers.Account = Renewal.Account INNER JOIN InvoiceLineItems ON Renewal.Id = InvoiceLineItems.invID WHERE ((Customers.company)='Foo Bar') ORDER BY Customers.Account;

The above Is my problem query which gets data from three tables, it gives the error "Column 'Customers.Account' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." This WAS working previously when it was linking only two tables and not getting the count from the InvoiceLineItems table, working query seen below. The change was to determine how many rows are in a third table, InvoiceLineItems, with an invID and Account that match Reneals Id and Account. I'll add some example data at the bottom of the post.

SELECT Customers.Account, Customers.Deptname, Renewal.Id, Customers.ID, Renewal.Startdate, Renewal.enddate, Renewal.Status, Renewal.PrintedOn FROM Customers INNER JOIN Renewal ON Customers.Account = Renewal.Account WHERE ((Customers.company)='Foo Bar')  ORDER BY Customers.Account;

I'm not all that proficient with SQL and the things I've tried after searching the issue either don't seem to apply or after applying produce their own new errors

Example Data with Proposed column being the count

Each Account has a unique invoice number counting up from one, but each invoice may have multiple line items on it. Let me know if I've provided enough information, and thanks in advance!

2

It is the presence of the "COUNT(InvoiceLineItems.invID)" that is causing the problem; not the presence of the third table.

The rule in SQL when using any aggregate function such as COUNT, AVG, SUM etc. is that any column that is in the SELECT list that is outside of the aggregate function must be listed in the GROUP by clause. So your query would need to be something like this (which may not necessarily be what your logic requires)

SELECT  Customers.Account ,
        Customers.Deptname ,
        Renewal.Id ,
        Customers.ID ,
        Renewal.Startdate ,
        Renewal.enddate ,
        Renewal.Status ,
        Renewal.PrintedOn ,
        COUNT(InvoiceLineItems.invID)
FROM    Customers
        INNER JOIN Renewal ON Customers.Account = Renewal.Account
        INNER JOIN InvoiceLineItems ON Renewal.Id = InvoiceLineItems.invID
WHERE   ( ( Customers.company ) = 'Foo Bar' )
GROUP BY
		Customers.Account ,
        Customers.Deptname ,
        Renewal.Id ,
        Customers.ID ,
        Renewal.Startdate ,
        Renewal.enddate ,
        Renewal.Status ,
        Renewal.PrintedOn 
ORDER BY Customers.Account;

Alternatively, if you don't need the count, you can remove that and then you wouldn't need to have the GROUP BY clause.

3

Thanks James, that worked perfectly!