CODEWITHSUNDEEP
sqlsql-server-2005
user1086159
user1086159

Reputation: 1055

SQL Grouping Error

I am running the below SQL but keep getting the error:-

'Column 'SRVS.dbo.BTQGLDistributionsWithProjectCodesHM.AccountNo' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

The code I am running is:-

SELECT         AccountNo, 
           TransDate, 
           JournalNo, 
           AuditTrail, 
           Description, 
           JnlAmount, 
           ACTINDX, 
           CONTRACTNAME, 
           PAPROJNUMBER,GROUPING(PAPROJNUMBER) as PAPROJNUMBER, 
           SUM(PAFeebillamount) as total
FROM       SRVS.dbo.BTQGLDistributionsWithProjectCodesHM
GROUP BY   PAPROJNUMBER with rollup
ORDER BY   GROUPING (PAPROJNUMBER

Can anyone point me in the correct direction on this?

Upvotes: 0

Views: 99

Answers (2)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

You have to include all columns in the SELECT statement that isn't included in an aggregate function in the GROUP BY clause like so:

SELECT AccountNo, TransDate, 
  JournalNo, AuditTrail, Description, 
  JnlAmount, ACTINDX, CONTRACTNAME, 
  PAPROJNUMBER, GROUPING(PAPROJNUMBER) as PAPROJNUMBER,
  SUM(PAFeebillamount) as total 
FROM SRVS.dbo.BTQGLDistributionsWithProjectCodesHM 
GROUP BY 
  AccountNo, TransDate, 
  JournalNo, AuditTrail, Description, 
  JnlAmount, ACTINDX, CONTRACTNAME, 
  PAPROJNUMBER
with rollup 
ORDER BY GROUPING (PAPROJNUMBER)

Upvotes: 1

podiluska
podiluska

Reputation: 51494

When using a GROUP BY statement, anything not using an aggregate must be specified in the group by clause.

SELECT     AccountNo,  
           TransDate,  
           JournalNo,  
           AuditTrail,  
           Description,  
           JnlAmount,  
           ACTINDX,  
           CONTRACTNAME,  
           PAPROJNUMBER,GROUPING(PAPROJNUMBER) as PAPROJNUMBER,  
           SUM(PAFeebillamount) as total 
FROM       SRVS.dbo.BTQGLDistributionsWithProjectCodesHM 
GROUP BY   
       AccountNo,  
       TransDate,  
       JournalNo,  
       AuditTrail,  
       Description,  
       JnlAmount,  
       ACTINDX,  
       CONTRACTNAME,
       PAPROJNUMBER with rollup 
ORDER BY   GROUPING (PAPROJNUMBER)

Upvotes: 2

Related Questions