SQL Error invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Question

Here is my query - created through SAP Query tool:

SELECT DISTINCT 
   T1.CardCode, 
   T1.CardName, 
   T1.Phone1, 
   T1.GroupCode, 
   T1.Territory,
   T2.Street, 
   T2.Block, 
   T2.City, 
   T2.ZipCode, 
   T2.County, 
   T2.State, 
   T2.Country,
   count(distinct(T3.DocEntry)) as NumberOfInvoices,
   sum(distinct(T4.GTotal)) as InvoiceTotal
FROM 
    [ASAP].[dbo].[OCRD] as T1
    INNER JOIN  [ASAP].[dbo].[CRD1] as T2 ON T1.CardCode = T2.CardCode
    INNER JOIN  [ASAP].[dbo].[OINV] as T3 on T1.CardCode = T3.CardCode
    INNER JOIN  [ASAP].[dbo].[INV1] as T4 on T3.DocEntry = T4.DocEntry
WHERE 
    T1.Territory = [%0] 
    AND T3.DocDate >= [%1] 
    AND T3.DocDate <= [%2] 
GROUP BY 
    T1.CardCode,
    T1.CardName, 
    T1.Phone1, 
    T1.GroupCode,
    T1.Territory,
    T2.Street,
    T2.Block, 
    T2.City, 
    T2.ZipCode, 
    T2.County, 
    T2.State, 
    T2.Country

This is the Error I recieve - You can clearly see I have the field in the Group By:

[Microsoft][SQL Native Client][SQL Server]Column 'ASAP.dbo.OCRD.Territory' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
[Microsoft][SQL Native Client][SQL Server]Statement 'Serv

Please advise

The sql you posted is fine. The error might be coming from something else in a larger transaction that this is a part of (?). IF you highlight just this portion of your transaction and run it, do you still get the error? — Chains, Aug 23 '11 at 21:57

BradC · Answer 1 · 2011-08-23T21:43:21.423

Remove Distinct. You're already grouping, so you don't need that. Not sure if that fixes your error or not.

I also highly suspect you don't want distinct inside your count() or sum(). Surely you want to add up all the invoice totals, not just the ones that are different? With the current syntax, if you had two different invoices with the same total, one would be skipped.

EDIT: If you are already passing in Territory from the user, why do you need to select that in the query? Just add it back on in the UI after the fact, or do:

SELECT ..., [%0] AS Territory, ...

instead of using the field.

SQL Error invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

1 Answers1