CODEWITHSUNDEEP
sql
asphy1
asphy1

Reputation: 37

SUM in stored procedure

I'm trying to calculate the SUM of MEMDISC but I keep getting this error on a stored procedure

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

This is my code

select
    CNUM, 
    DESCRIPT, 
    ZCONTDESC,
    substring(str(OCCDATE),9,2) + '/'+ substring(str(OCCDATE),7,2) + '/' + substring(str(OCCDATE),3,4),
    COWNNUM,
    CAST((CAST(substring(str(ANVDATE),3,4) AS INT) -1) AS CHAR(4))+'-' +substring(str(ANVDATE),3,4),
    sum(MEMDISC)
from 
    dbo.ZZ

What it should look like?

Upvotes: 1

Views: 1226

Answers (1)

Tony G. Bolaño
Tony G. Bolaño

Reputation: 471

As suggested in comments by @Lucky and @LukStorms, when you use aggregation functions you need to GROUP BY all fields which are not included in an aggregation function. In your case you need something like this:

select CNUM, DESCRIPT, ZCONTDESC,
   substring(str(OCCDATE),9,2) + '/'+ substring(str(OCCDATE),7,2) + '/' + substring(str(OCCDATE),3,4),
   COWNNUM,
   CAST((CAST(substring(str(ANVDATE),3,4) AS INT) -1) AS CHAR(4))+'-' +substring(str(ANVDATE),3,4),
   sum(MEMDISC)
  from dbo.ZZ
 group by CNUM, DESCRIPT, ZCONTDESC,
   substring(str(OCCDATE),9,2) + '/'+ substring(str(OCCDATE),7,2) + '/' + substring(str(OCCDATE),3,4),
   COWNNUM,
   CAST((CAST(substring(str(ANVDATE),3,4) AS INT) -1) AS CHAR(4))+'-' +substring(str(ANVDATE),3,4),

Upvotes: 1

Related Questions