System Functions with Error: Not in Agg/Group By

Question

Using Microsoft SQL Server 2012.

I have a table called Loan with columns CustomerFname, CustomerLname, PropertyAddress, City, State, BankruptcyAttorneyName, UPB, and LoanDate.

For the sake of this question, I have to write a query to retrieve loan number, customer first name, customer last name, property address, and bankruptcy attorney name.

All of the records that have the same attorney name have to be together, then the customer last name in order from Z to A.

Here's the code I have so far:

SELECT LoanNumber, 
       CustomerFname, 
       CustomerLname, 
       PropertyAddress, 
       BankruptcyAttorneyName
FROM Loan
GROUP BY BankruptcyAttorneyName
ORDER BY CustomerLname DESC

Basically getting the error for all the columns not named BankruptcyAttorneyName.

invalid in select list because not contained in either agg function or GROUP BY clause.

I understand the error, but am unsure of how to go about it. I'm sure there's an easy way, but any help would be appreciated.

GROUP BY is always (almost) combined with aggregate functions, like SUM, COUNT and MAX/MIN. I you don't need those, you probably don't need GROUP BY either. — jarlh
– jarlh, Commented Jan 18, 2016 at 8:18
I guess that just may be it honestly. I took out the GROUP BY and seems to be working OK. Thank you. — smul86
– smul86, Commented Jan 18, 2016 at 8:22

Mureinik · Accepted Answer · 2016-01-18 08:16:12Z

1

It sounds as though you don't need grouping at all - just order the result by the attorney name first and then by the customer's last name:

SELECT     LoanNumber, 
           CustomerFname, 
           CustomerLname, 
           PropertyAddress, 
           BankruptcyAttorneyName
FROM       Loan
ORDER BY   BankruptcyAttorneyName ASC, CustomerLname DESC -- Here!

answered Jan 18, 2016 at 8:16

Mureinik

316k54 gold badges400 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

smul86 Over a year ago

I basically still keep getting that same error. "Column 'Loan.LoanNumber' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. And then the error repeats with CustomerFname, CustomerLname, and PropertyAddress.

Mureinik Over a year ago

Without a group by clause? impossible. Are you sure you copied the query correctly?

smul86 Over a year ago

Never mind, you were right. My apologies. Thank you!

Collectives™ on Stack Overflow

System Functions with Error: Not in Agg/Group By

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related