August 20, 2009 at 12:37 am
Hi There,
I am getting many errors because my group by clause is incorrect. Can someone please help by writing how the Group By clause should look?
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 SUM(b.TRXQTY) * - 1
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 SUM(b.TRXQTY) * - 1
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 SUM(b.TRXQTY) * - 1
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 SUM(b.TRXQTY) * - 1
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 SUM(b.TRXQTY) * - 1
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 SUM(b.TRXQTY) * - 1
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)
AND (DATEPART(MM, b.DOCDATE) = DATEPART(mm, GETDATE()))
AND (DATEPART(year, b.DOCDATE) = DATEPART(year, GETDATE())))
THEN SUM(b.TRXQTY) * - 1
END AS CurrentMonth,
CASE
WHEN f.ITEMNMBR = d.ITEMNMBR
AND f.LOCNCODE = ''
THEN f.QTYONORD
END AS QtyOnOrder,
CASE
WHEN f.ITEMNMBR = d.ITEMNMBR
AND f.SFTYSTCKQTY > 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)
GROUP BY d.ITEMNMBR,
d.ITEMDESC,
d.ABCCODE,
d.UOMSCHDL,
e.VENDORID,
e.PLANNINGLEADTIME,
f.QTYONORD,
f.SFTYSTCKQTY,
f.QTYONHND,
f.PRCHSNGLDTM
August 20, 2009 at 12:43 am
Hi Paula,
You need to include your CASE statements in your group by clause for your query to work. eg:
SELECT TOP 10
[VOLUME] = count(1),
[ISVALID] =
CASE BOT_DR_CLIENT_NO
WHEN 1 THEN 'YES' ELSE 'NO' END
from BO_TRANSFER
GROUP BYCASE BOT_DR_CLIENT_NO
WHEN 1 THEN 'YES' ELSE 'NO' END
Cheers 🙂
----------------------------------------------------------------------------------------------
Struggling to find the question?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
August 20, 2009 at 1:01 am
OK. I'll give it a go!!
August 20, 2009 at 7:32 pm
OK. I have changed the code, but I am still getting the following error:
Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.
Here is the ammended code:
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 SUM(b.TRXQTY) * - 1
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 SUM(b.TRXQTY) * - 1
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 SUM(b.TRXQTY) * - 1
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 SUM(b.TRXQTY) * - 1
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 SUM(b.TRXQTY) * - 1
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 SUM(b.TRXQTY) * - 1
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,
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)
GROUP BY 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 SUM(b.TRXQTY) * - 1 END,
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 SUM(b.TRXQTY) * - 1 END,
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 SUM(b.TRXQTY) * - 1 END,
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 SUM(b.TRXQTY) * - 1 END,
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 SUM(b.TRXQTY) * - 1 END,
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 SUM(b.TRXQTY) * - 1 END,
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,
CASE
WHEN f.ITEMNMBR = d.ITEMNMBR
AND f.LOCNCODE = ''
THEN f.QTYONHND END,
CASE
WHEN f.ITEMNMBR = d.ITEMNMBR
AND f.LOCNCODE = ''
THEN f.PRCHSNGLDTM / 7 END,
e.VENDORID,
DATEPART(MM, GETDATE())
August 20, 2009 at 8:03 pm
WilliamB (8/20/2009)
Hi Paula,You need to include your CASE statements in your group by clause for your query to work. eg:
SELECT TOP 10
[VOLUME] = count(1),
[ISVALID] =
CASE BOT_DR_CLIENT_NO
WHEN 1 THEN 'YES' ELSE 'NO' END
from BO_TRANSFER
GROUP BYCASE BOT_DR_CLIENT_NO
WHEN 1 THEN 'YES' ELSE 'NO' END
Cheers 🙂
Ummmm.... no.... not in this (no pun intended) case. Please see the following for how to do Cross Tabs.
http://www.sqlservercentral.com/articles/T-SQL/63681/
Just change the SUM to MAX in the examples for this one.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 20, 2009 at 8:05 pm
Alright... give me a bit and I'll show you how to do the pre-aggregated cross tab from the article link I just posted. It'll also improve performance drastically.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 20, 2009 at 9:21 pm
I'm sorry. But I am a bit lost here. I think I need the sum aggregate function. I don't think max will give me what I'm looking for.
August 20, 2009 at 9:28 pm
paula (8/20/2009)
I'm sorry. But I am a bit lost here. I think I need the sum aggregate function. I don't think max will give me what I'm looking for.
I understand the confusion... Max, Sum... it won't matter after the preaggregation is done because we'd be taking the MAX of a single pre-aggregated SUM row, but I'll leave it at SUM just so as not to confuse anyone.
Obviously, I don't have your data so I couldn't test it. I did the best I could but I believe you'll be amazed at what a pre-aggregated sub query (CTE's in this case) can actually do. If you have the time, the article I gave you the link to explains this pretty well. If anyone tries to talk you into using PIVOT, show them the article because it will show them that PIVOT is slower, uglier, and generally more difficult to write and maintain.
Here's the code... remember... I couldn't test it...
WITH
ctePreAggB AS
(--==== Pre-aggregate all data before formatting with the cross tab below in cteB
SELECT ItemNmbr,
DeltaMonth = DATEDIFF(mm,GETDATE(),DocDate),
SumTrxQty = -SUM(TrxQty)
FROM dbo.IV30300
WHERE DocType IN (1,6)
AND TrxSorce LIKE 'IVADJ%'
AND (DocNmbr LIKE 'RTS%' OR DocNmbr LIKE 'ADJ%')
AND TrxQty = DATEADD(mm,DATEDIFF(mm,0,GETDATE())-6,0)
GROUP BY
ItemNmbr,
DATEDIFF(mm,GETDATE(),DocDate)
)
,
cteB AS
(
SELECT ItemNmbr,
Month6 = SUM(CASE WHEN DeltaMonth = -6 THEN SumTrxQty ELSE 0 END),
Month5 = SUM(CASE WHEN DeltaMonth = -5 THEN SumTrxQty ELSE 0 END),
Month4 = SUM(CASE WHEN DeltaMonth = -4 THEN SumTrxQty ELSE 0 END),
Month3 = SUM(CASE WHEN DeltaMonth = -3 THEN SumTrxQty ELSE 0 END),
Month2 = SUM(CASE WHEN DeltaMonth = -2 THEN SumTrxQty ELSE 0 END),
Month1 = SUM(CASE WHEN DeltaMonth = -1 THEN SumTrxQty ELSE 0 END),
CurrentMonth = SUM(CASE WHEN DeltaMonth = 0 THEN SumTrxQty ELSE 0 END)
FROM ctePreAggB
GROUP BY ItemNmbr
)
SELECT d.ItemNmbr,
d.ItemDesc,
d.AbcCode,
d.UoMSchdl,
b.Month6,
b.Month5,
b.Month4,
b.Month3,
b.Month2,
b.Month1,
b.CurrentMonth,
QtyOnOrder = CASE WHEN f.LocnCode = '' THEN f.QtyOnOrd ELSE 0 END,
SafetyStock = CASE WHEN f.SftyStckQty > 0 THEN f.SftyStckQty ELSE 0 END,
QtyOnHand = CASE WHEN f.LocnCode = '' THEN f.QtyOnHnd ELSE 0 END,
Planning_Lead_Time = CASE WHEN f.LocnCode = '' THEN f.PrchsngLdTm/7 ELSE 0 END,
Supplier = e.VendorID,
e.PlanningLeadTime,
This_Month = DATEPART(mm,GETDATE())
FROM dbo.IV00101 AS d
LEFT OUTER JOIN dbo.IV00103 AS e ON d.ItemNmbr = e.ItemNmbr
LEFT OUTER JOIN cteB AS b ON d.ItemNmbr = b.ItemNmbr
LEFT OUTER JOIN IV00102 AS f ON d.ItemNmbr = f.ItemNmbr
;
--Jeff Moden
Change is inevitable... Change for the better is not.
August 20, 2009 at 9:33 pm
Sorry... I left out one "%" wildcard in the code above... I've fixed the code above.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 20, 2009 at 9:33 pm
Thank you so much for spending the time to do that. Can I ask you for one more favour? Can you get the code I wrote to work also?
August 20, 2009 at 9:45 pm
paula (8/20/2009)
Thank you so much for spending the time to do that. Can I ask you for one more favour? Can you get the code I wrote to work also?
I could... but it will run about 10 - 20 times slower.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 20, 2009 at 9:56 pm
That's ok. I really really want it to work. I have been working on it for ages and would really appreciate it if you could.
Thank you so much!!!
August 20, 2009 at 10:05 pm
paula (8/20/2009)
That's ok. I really really want it to work. I have been working on it for ages and would really appreciate it if you could.Thank you so much!!!
Are you by any chance trying to write this for Oracle? The highly abbreviated column names make it look so. If true, the code will also work in Oracle.
And, to be honest, I'm just not going to help someone write code the wrong way. Sorry...
After thinking about it, just move the SUM from inside the CASE statements in your code to outside the CASE Statments and copy everything else into the GROUP BY.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 20, 2009 at 10:24 pm
OK. I understand. Could you show how that looks for just one of the case statements in the group by clause
August 20, 2009 at 10:31 pm
Heh... you say you understand, but I just can't bring myself to show you how to do it wrong, Paula. Sorry...
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply