SQL Query is invalid in the select list

Question

I dont know why this is coming up as invalid and I can not figure it out. I was given a legacy database as my supervisor left and I am in charge until someone comes to replace him. I am trying to run this query...

    SELECT     tblM.guidRId, SUM(dbo.tblCH.curTotalCost) AS curValue
FROM         tblCH INNER JOIN
                      tblM ON tblCH.guidMId = tblM.guidMId INNER JOIN
                      ViewLM ON tblM.strNumber = ViewLM.strNumber
WHERE     (tblM.guidRId = '4d832bc8-1827-4054-9896-6111844b0f26')

The error I keep getting is...Msg 8120, Level 16, State 1, Line 1 Column 'tblM.guidRId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Why is this error occuring?

Answer 1

Because you need a GROUP BY if you are going to use an aggregate functon like SUM() [or COUNT, AVG(), etc...] with another non-aggregate column:

SELECT tblM.guidRId, SUM(dbo.tblCH.curTotalCost) AS curValue
FROM   tblCH 
INNER JOIN tblM 
    ON tblCH.guidMId = tblM.guidMId 
INNER JOIN ViewLM 
    ON tblM.strNumber = ViewLM.strNumber
WHERE  tblM.guidRId = '4d832bc8-1827-4054-9896-6111844b0f26'
GROUP BY tblM.guidRId;

Answer 2

You are forgetting to group guidRId. (you are aggregating the data)

SELECT     
    tblM.guidRId, 
    SUM(dbo.tblCH.curTotalCost) AS curValue
FROM         
    tblCH 
    INNER JOIN tblM ON tblCH.guidMId = tblM.guidMId 
    INNER JOIN ViewLM ON tblM.strNumber = ViewLM.strNumber
WHERE  
    tblM.guidRId = '4d832bc8-1827-4054-9896-6111844b0f26'
GROUP BY tblM.guidRId

Answer 3

Try:

SELECT
 tblM.guidRId, SUM(dbo.tblCH.curTotalCost) AS curValue
FROM
 tblCH
  INNER JOIN tblM
   ON tblCH.guidMId = tblM.guidMId
  INNER JOIN ViewLM
   ON tblM.strNumber = ViewLM.strNumber
WHERE (tblM.guidRId = '4d832bc8-1827-4054-9896-6111844b0f26')
GROUP BY tblM.guidRId