SQLTeam.com | Weblogs | Forums

"Group by" Error


#1

Hi everyone.

I'm new in SQL world.
I'm doing a simple query to show the "SumApplied" paid before the date that i give as parameter.
But i'm getting an error, and i don't understand why.
The error is: "Msg 8120, Level 16, State 1, Line 6
Column 'OINV.DocNum' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

My query:

if exists (SELECT OV.DOCNUM FROM RCT2 RT
INNER JOIN OINV OV ON OV.DOCENTRY = RT.DOCENTRY
INNER JOIN ORCT RC ON RC.DOCENTRY = RT.DOCNUM
WHERE RC.DOCDATE <= '2015-06-01')

SELECT SUM(RT.SUMAPPLIED), OV.DOCNUM FROM RCT2 RT
INNER JOIN OINV OV ON OV.DOCENTRY = RT.DOCENTRY
INNER JOIN ORCT RC ON RC.DOCENTRY = RT.DOCNUM
WHERE RC.DOCDATE <= '2015-06-01'
ORDER BY OV.DOCNUM
else select null

Can anyone help me found?

Thanks!
Best Regards.

Anita


#2

between WHERE and ORDER BY add:

GROUP BY OV.DOCNUM


#3

oh, silly me, I've put the "group by" clause a few seconds ago but i must have deleted and don't see. I'm looking for "order by" and get distracted, i was certain that the group by clause was there. Sorry.

thank you gbritton :slight_smile: