Help on "GROUP BY clause" error

Question

Hi Guys..

Wish anybody can help me with this error "Column 'Sales.No_' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."... I spent few days to understand this error but failed :( My Query:-

SELECT SH.[No_], SH.[Sell-to Customer No_], SH.[Sell-to Contact No_],
SH.[Sell-to Customer Template Code], MAX (A.[Version No_]) 
FROM [Sales] AS SH 
LEFT JOIN [Sales Archive] A ON (SH.[Document Type] = A.[Document Type] 
                 AND SH.[No_]=A.[No_] 
                 AND SH.[Doc_ No_ Occurrence]=A.[Doc_ No_ Occurrence]) 
WHERE (SH.[Document Type]='0' and SH.[Order]='1')

Answer 1

The "MAX" function requires a group by if you have an non-aggregate columns. So you will need to add a group by sh.[No_]....

Also I have reformatted your query so I can read it easier -hope that is ok-

select SH.[No_]
, SH.[Sell-to Customer No_]
, SH.[Sell-to Contact No_]
, SH.[Sell-to Customer Template Code]
, MAX (A.[Version No_]) 
from [Sales] AS SH 
    LEFT JOIN [Sales Archive] A ON 
        (SH.[Document Type] = A.[Document Type] 
          AND SH.[No_]=A.[No_] 
          AND SH.[Doc_ No_ Occurrence]=A.[Doc_ No_ Occurrence]
        ) 
where (SH.[Document Type]='0' and SH.[Order]='1')
group by SH.[No_]
       , SH.[Sell-to Customer No_]
       , SH.[Sell-to Contact No_]
       , SH.[Sell-to Customer Template Code]

Answer 2

It's because you've used an aggregate function (MAX), so the remaining selected columns must also be using aggregate functions or in a group by clause. eg

select SH.[No_],SH.[Sell-to Customer No_],SH.[Sell-to Contact No_],
SH.[Sell-to Customer Template Code],MAX (A.[Version No_])
from [Sales] AS SH LEFT JOIN [Sales Archive] A ON (SH.[Document Type] = A.[Document Type]
AND SH.[No_]=A.[No_] AND SH.[Doc_ No_ Occurrence]=A.[Doc_ No_ Occurrence])
where (SH.[Document Type]='0' and SH.[Order]='1')
group by SH.[No_],SH.[Sell-to Customer No_],SH.[Sell-to Contact No_],
SH.[Sell-to Customer Template Code]