We help IT Professionals succeed at work.

SQL Query Syntax

Lawrence Salvucci
Lawrence Salvucci asked
on
I'm trying to use the GROUP BY statement but I'm getting an error message when I try to execute my query. The error states: Column 'dbo.jomast.fsono' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Not sure I understand what I'm doing wrong. Can anyone shed some light on this?

Here's the query:

SELECT        J2.fjobno AS JobOrderNo, J2.fsono + LTRIM(RTRIM(J2.fkey)) AS SOKey, I.fcpro_name AS WorkCenterName
FROM            dbo.jodrtg AS J1 INNER JOIN
                         dbo.inwork AS I ON J1.fpro_id = I.fcpro_id INNER JOIN
                         dbo.jomast AS J2 ON J1.fjobno = J2.fjobno
WHERE        (I.fcpro_name LIKE '%-G%')
GROUP BY J2.fjobno

Open in new window

Comment
Watch Question

View Solution Only

Database and Application Developer
Commented: 
SELECT        J2.fjobno AS JobOrderNo, J2.fsono + LTRIM(RTRIM(J2.fkey)) AS SOKey, I.fcpro_name AS WorkCenterName
FROM            dbo.jodrtg AS J1 INNER JOIN
                         dbo.inwork AS I ON J1.fpro_id = I.fcpro_id INNER JOIN
                         dbo.jomast AS J2 ON J1.fjobno = J2.fjobno
WHERE        (I.fcpro_name LIKE '%-G%')
GROUP BY J2.fjobno, J2.fsono + LTRIM(RTRIM(J2.fkey)),  I.fcpro_name

Open in new window


You need to include J2.fsono + LTRIM(RTRIM(J2.fkey)) and I.fcpro_name in GROUP BY or

Apply an aggregate function to them like:
SELECT        J2.fjobno AS JobOrderNo, FIRST( J2.fsono + LTRIM(RTRIM(J2.fkey))) AS SOKey, MAX(I.fcpro_name)) AS WorkCenterName