CODEWITHSUNDEEP
sqlsql-servergroup-by
arash moeen
arash moeen

Reputation: 4693

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

I'm trying to select a bunch of patients with their unit and division and I want to group the result by unit name, but this code doesn't execute and gives the error as the topic of this question. 

SELECT TOP (100) PERCENT 
    Pat.PatName AS Name, 
    srvDiv.sgMType AS Perkhidmatan,
    Pat.PatMRank AS Pangkat, 
    Pat.PatMilitaryID AS [No# Tentera],
    unt.untName AS Unit,
    fct.pesStatusCode as StatusCode,
    fct.pesSignedDate AS SignedDate

FROM dbo.FactPES AS fct INNER JOIN 
    dbo.DimPatient AS Pat ON fct.pesPatID = Pat.PatID LEFT OUTER JOIN
    dbo.DimUnit AS unt ON fct.pesUnitID = unt.untID LEFT OUTER JOIN 
    dbo.DimServiceDiv AS srvDiv ON fct.pesServiceDivID = srvDiv.sgID 
GROUP BY unt.untName
HAVING (deas.diDate BETWEEN  
    CONVERT(DATETIME, @FromDate, 102) 
AND 
    CONVERT(DATETIME, @ToDate, 102))

I assume it's because unt.UntName is in my left join so I can't use it outside the join maybe ? I'm a bit confused because when I put it like this it works:

GROUP BY unt.untName, Pat.PatName, srvDiv.sgMType, 
    Pat.PatMRank, Pat.PatMilitaryID, unt.untName, 
    fct.pesStatusCode, fct.pesSignedDate

Any help is appreciated

Upvotes: 0

Views: 401

Answers (2)

James K. Lowden
James K. Lowden

Reputation: 7837

First, please don't use TOP (100) PERCENT; it hurts even to read.

Second, your query contains no aggregate function, no SUM or COUNT for example. When you say you want to "group by unit name", I suspect you may simply want the results sorted by unit name. In that case, you want ORDER BY instead. (The advice from other to study what group by does is well taken.)

Finally, you might not need those CONVERT functions at the end, depending on your DBMS.

Upvotes: 1

Praveen Nambiar
Praveen Nambiar

Reputation: 4892

Whenever you use a GROUP BY - it should be present in the SELECT statement as a column. And if you do not want to contain it in a GROUP BY use it as an AGGREGATE column in SELECT.

So now in your case the second GROUP BY stated in your question will work.

Read this to understand more about GROUP BY

Upvotes: 0

Related Questions