Syntax error with sum in sql code

Question

I'm having issues with my SQL Server code. I am trying to get results to list by the first 4 columns of the select statement. I keep getting syntax errors and am unsure as to why.

What I want it to do is pull by the entityid, acctnum, period that I define but, I don't want to see the period in the results as I want them to be summed together. IE I want see the total activity for the activity column instead of rows for each period.

SELECT 
    ENTITYID, ACCTNUM, ACCTNAME, ACTIVITY
FROM
    (SELECT  
         g.ENTITYID AS 'ENTITYID'
         g.ACCTNUM AS 'ACCTNUM'
         SUM(g.ACTIVITY) AS 'ACTIVITY'
         h.ACCTNAME AS 'ACCTNAME'
     FROM 
         SQLDATA.DBO.GLSUM  g
     INNER JOIN 
         SQLDATA.DBO.GACC h ON g.ACCTNUM = h.ACCTNUM
     WHERE   
         g.ENTITYID = '85000'
         g.PERIOD < '201703'
         g.ACCTNUM = '569300000')

Are you going to share the error message with us? You are missing a closing ) somewhere. — OldProgrammer
– OldProgrammer, Commented Apr 7, 2017 at 17:39
There is an error in the SQL Statement, Incorrect syntax near 'g' — SQLISHARD
– SQLISHARD, Commented Apr 7, 2017 at 17:39

marc_s · Accepted Answer · 2017-04-07 18:51:28Z

2

You need a GROUP BY clause https://learn.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql, commas in the SELECT list, AND keywords between WHERE elements, and the outer select seems a bit redundant.

SELECT  
    g.ENTITYID AS 'ENTITYID',
    g.ACCTNUM AS 'ACCTNUM',
    SUM(g.ACTIVITY) AS 'ACTIVITY',
    h.ACCTNAME AS 'ACCTNAME'
FROM 
    SQLDATA.DBO.GLSUM  g
INNER JOIN 
    SQLDATA.DBO.GACC h ON g.ACCTNUM = h.ACCTNUM
WHERE   
    g.ENTITYID = '85000'
    AND g.PERIOD < '201703'
    AND g.ACCTNUM = '569300000' 
GROUP BY  
    g.ENTITYID,
    g.ACCTNUM,
    h.ACCTNAME

edited Apr 7, 2017 at 18:51

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Apr 7, 2017 at 17:41

Gareth Lyons

1,97214 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SQLISHARD Over a year ago

I still get the same "There is an error in the SQL statement, Incorrect syntax near 'g'."

LONG · Accepted Answer · 2017-04-07 17:47:04Z

0

DECLARE @a int 
SET @a = select sum(g.ACTIVITY) 
         from SQLDATA.DBO.GLSUM  
         WHERE ENTITYID = '85000' and
               PERIOD < '201703' and
               ACCTNUM = '569300000' 



SELECT  

           g.ENTITYID AS 'ENTITYID'
           g.ACCTNUM AS 'ACCTNUM'
           @a AS 'ACTIVITY'
           h.ACCTNAME AS 'ACCTNAME'

FROM SQLDATA.DBO.GLSUM  g
INNER JOIN SQLDATA.DBO.GACC h
ON g.ACCTNUM = h.ACCTNUM

WHERE   

           g.ENTITYID = '85000'
           g.PERIOD < '201703'
           g.ACCTNUM = '569300000'

answered Apr 7, 2017 at 17:47

LONG

4,6302 gold badges21 silver badges41 bronze badges

Collectives™ on Stack Overflow

Syntax error with sum in sql code

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related