sql server 2008 - i want to get all column of Accounts table with this query but it is giving error

Question

select Accounts.name,  Accounts.regno Accounts.model , Accounts.slacc, count (servicing.dt) as total 
from Accounts l,eft 
outer join servicing on Accounts.slacc = servicing.slacc 
group by Accounts.slacc,Accounts.name 

The error message is

Major Error 0x80040E14, Minor Error 25515 
> select Accounts.name,Accounts .model , Accounts.regno, Accounts.slacc, count (servicing.dt) as total from Accounts left outer join servicing on Accounts.slacc = servicing.slacc group by Accounts.slacc,Accounts.name 
In aggregate and grouping expressions, the SELECT clause can contain only 
aggregates and grouping expressions. [ Select clause = Accounts,model ]

Answer 1

Your query has a group by clause. If you use a group by clause in the query, then every column in the select statement has to do one of two things - either it has to be part of the group by list, or it has to be an aggregate of some kind (Sum, Count, Avg, Max, etc). If you don't do this, SQL doesn't know what to do with the column. In your case Accounts.regno and Accounts.model are listed in the select, but they are not in the group by clause and they are not aggregates - hence your error.

Assume for the moment you have two account records with the same account name and slacc, but different Regno (or model). The group by clause says they have to be joined into one record for display, but you haven't told SQL how to do that. It doesn't matter if the data isn't like that, SQL looks for possible errors first.

In this case, you probably just want all the details grouped. The simplest way is just to make sure you add all the columns needed to the group by, like this

select Accounts.name, Accounts.regno, Accounts.model, Accounts.slacc, count(servicing.dt) as total 
from Accounts 
   left outer join servicing on Accounts.slacc = servicing.slacc 
group by Accounts.slacc, Accounts.name, Accounts.regno, Accounts.model

This will fix the error, but does extra grouping you don't need, and would get very cumbersome if you had a lot more columns you wanted from account, as you'd have to add them all. Another way to handle it is to use the minimum amount of columns for the group query, then join the result of that to your main query to get the other columns. This would probably look something like this

select Accounts.name, Accounts.regno, Accounts.model, Accounts.slacc, Totals.Total 
from Accounts
   left outer join 
     ( Select slacc, count(dt) as total
       from servicing
       group by slacc
     ) Totals on Totals.slacc = Accounts.slacc