Column '(x) ' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Question

Column '(x) ' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

paula_sql SSCrazy Points: 2665 More actions · Answer 1

That's OK

Thank you for all the help you have given me. I really appreciate it. I hope my skills can improve to be like yours some day. I can tell you are a true professional.

Thanks

WilliamBendall SSCrazy Points: 2591 More actions · Answer 2

Thanks Jeff, I've learned something new today! 🙂

----------------------------------------------------------------------------------------------
Struggling to find the question?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

ta.bu.shi.da.yu Hall of Fame Points: 3985 More actions · Answer 3

My posted query isn't going to work. Didn't realise that you can't use an outer reference in a group by clause in SQL Server 2005.

Random Technical Stuff[/url]

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 4

WilliamB (8/21/2009)
Thanks Jeff, I've learned something new today! 🙂

Very cool. Thanks for the feedback, William.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

paula_sql SSCrazy Points: 2665 More actions · Answer 5

paula_sql

SSCrazy

Points: 2665

August 23, 2009 at 5:22 pm

#1043413

What is an outer reference

ta.bu.shi.da.yu Hall of Fame Points: 3985 More actions · Answer 6

An outer reference is a reference to a table that isn't in the from clause.

e.g.

SELECT A.colA, B.colB

FROM PrimaryTable A where A.colValue in (select B.colValue from OuterRefTable B where A.colRef=B.colRef)

The table OuterRefTable in the subselect is an outer reference.

Random Technical Stuff[/url]

paula_sql SSCrazy Points: 2665 More actions · Answer 7

paula_sql

SSCrazy

Points: 2665

August 24, 2009 at 4:41 pm

#1043933

Thanks

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 8

It's also known as a "Correlated Sub Query".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 9

ta.bu.shi.da.yu (8/24/2009)
An outer reference is a reference to a table that isn't in the from clause.
e.g.
SELECT A.colA, B.colB
FROM PrimaryTable A where A.colValue in (select B.colValue from OuterRefTable B where A.colRef=B.colRef)
The table OuterRefTable in the subselect is an outer reference.

That's exactly backwards 😀

The outer reference in that example is A.colRef

The clearest example I could Google in ten seconds is this

It also nicely ties in Jeff's correct 'correlated sub-query' reference.

The correlation in this example between the inner SELECT (B) and the outer SELECT (A) is A.colRef = B.colRef. In that correlation, B.colRef is the inner reference and A.colRef is the outer reference.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

zubamark Right there with Babe Points: 735 More actions · Answer 10

try this one.

SELECT ITEMNMBR, ITEMDESC, ABCCODE, UOMSCHDL, CurrentMonth,

QtyOnOrder, SafetyStock, QtyOnHand, Planning_Lead_Time, Supplier, PLANNINGLEADTIME, This_Month,

SUM(Month6) * - 1 Month6,

SUM(Month5) * - 1 Month5,

SUM(Month4) * - 1 Month4,

SUM(Month3) * - 1 Month3,

SUM(Month2) * - 1 Month2,

SUM(Month1) * - 1 Month1

(SELECT d.ITEMNMBR,d.ITEMDESC,d.ABCCODE,d.UOMSCHDL,

CASE WHEN (b.DOCTYPE IN (1, 6)

AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) 'IVADJRTS')

AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) 'IVADJADJ')

AND (b.TRXQTY < 0)

AND (DATEPART(MM, b.DOCDATE) = DATEPART(mm, GETDATE()) - 6)

AND (DATEPART(year, b.DOCDATE) = DATEPART(year, GETDATE())))

THEN IsNull(b.TRXQTY,0) END AS Month6,

CASE WHEN ((b.ITEMNMBR = d.ITEMNMBR)

AND (b.DOCTYPE IN (1, 6))

AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) 'IVADJRTS')

AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) 'IVADJADJ')

AND (b.TRXQTY < 0)

AND (DATEPART(MM, b.DOCDATE) = DATEPART(mm, GETDATE()) - 5)

AND (DATEPART(year, b.DOCDATE) = DATEPART(year, GETDATE())))

THEN IsNull(b.TRXQTY,0) END AS Month5,

CASE WHEN ((b.ITEMNMBR = d.ITEMNMBR)

AND (b.DOCTYPE IN (1, 6))

AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) 'IVADJRTS')

AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) 'IVADJADJ')

AND (b.TRXQTY < 0)

AND (DATEPART(MM, b.DOCDATE) = DATEPART(mm, GETDATE()) - 4)

AND (DATEPART(year, b.DOCDATE) = DATEPART(year, GETDATE())))

THEN IsNull(b.TRXQTY,0) END AS Month4,

CASE WHEN ((b.ITEMNMBR = d.ITEMNMBR)

AND (b.DOCTYPE IN (1, 6))

AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) 'IVADJRTS')

AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) 'IVADJADJ')

AND (b.TRXQTY < 0)

AND (DATEPART(MM, b.DOCDATE) = DATEPART(mm, GETDATE()) - 3)

AND (DATEPART(year, b.DOCDATE) = DATEPART(year, GETDATE())))

THEN IsNull(b.TRXQTY,0) END AS Month3,

CASE WHEN ((b.ITEMNMBR = d.ITEMNMBR)

AND (b.DOCTYPE IN (1, 6))

AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) 'IVADJRTS')

AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) 'IVADJADJ')

AND (b.TRXQTY < 0)

AND (DATEPART(MM, b.DOCDATE) = DATEPART(mm, GETDATE()) - 2)

AND (DATEPART(year, b.DOCDATE) = DATEPART(year, GETDATE())))

THEN IsNull(b.TRXQTY,0) END AS Month2,

CASE WHEN ((b.ITEMNMBR = d.ITEMNMBR)

AND (b.DOCTYPE IN (1, 6))

AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) 'IVADJRTS')

AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) 'IVADJADJ')

AND (b.TRXQTY < 0)

AND (DATEPART(MM, b.DOCDATE) = DATEPART(mm, GETDATE()) - 1)

AND (DATEPART(year, b.DOCDATE) = DATEPART(year, GETDATE())))

THEN IsNull(b.TRXQTY,0) END AS Month1,

CASE WHEN ((b.ITEMNMBR = d.ITEMNMBR)

AND (b.DOCTYPE IN (1, 6))

AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) 'IVADJRTS')

AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) 'IVADJADJ')

AND (b.TRXQTY 0

THEN f.SFTYSTCKQTY END AS SafetyStock,

CASE WHEN f.ITEMNMBR = d.ITEMNMBR AND f.LOCNCODE = ''

THEN f.QTYONHND END AS QtyOnHand,

CASE WHEN f.ITEMNMBR = d.ITEMNMBR AND f.LOCNCODE = ''

THEN f.PRCHSNGLDTM / 7 END AS Planning_Lead_Time,

e.VENDORID AS Supplier, e.PLANNINGLEADTIME,

DATEPART(MM, GETDATE()) AS This_Month

FROM IV00101 AS d LEFT OUTER JOIN IV00103 AS e ON d.ITEMNMBR = e.ITEMNMBR LEFT OUTER JOIN IV30300 AS b ON d.ITEMNMBR = b.ITEMNMBR LEFT OUTER JOIN IV00102 AS f on d.itemnmbr = f.itemnmbr WHERE (d.ITEMTYPE 2)

)A

GROUP BY ITEMNMBR, ITEMDESC, ABCCODE, UOMSCHDL, CurrentMonth,

QtyOnOrder, SafetyStock, QtyOnHand, Planning_Lead_Time, Supplier,

PLANNINGLEADTIME, This_Month

ta.bu.shi.da.yu Hall of Fame Points: 3985 More actions · Answer 11

Paul White (8/25/2009)
ta.bu.shi.da.yu (8/24/2009)
An outer reference is a reference to a table that isn't in the from clause.
e.g.
SELECT A.colA, B.colB
FROM PrimaryTable A where A.colValue in (select B.colValue from OuterRefTable B where A.colRef=B.colRef)
The table OuterRefTable in the subselect is an outer reference.
That's exactly backwards 😀
The outer reference in that example is A.colRef
The clearest example I could Google in ten seconds is this
It also nicely ties in Jeff's correct 'correlated sub-query' reference.
The correlation in this example between the inner SELECT (B) and the outer SELECT (A) is A.colRef = B.colRef. In that correlation, B.colRef is the inner reference and A.colRef is the outer reference.
Paul

Gah! I guess you live and learn. I seem to be making a lot of mistakes lately 🙁

Random Technical Stuff[/url]