How to properly group using SQL

Question

Post reply

How to properly group using SQL

Dedra Wagoner

Newbie

Points: 7
More actions
March 9, 2009 at 3:52 pm

#128799

I'm fairly new to the SQL language and need help with the following query...
Returns:
8773 10A0116Z-044910 1 778.000 916.200 2511-0296044900 2 2 .5 .5 02/10/2009 -498.719
8773 10A0116Z-044910 1 778.000 916.200 2511-0296044900 2 2 .5 .5 02/10/2009 500.000
8773 10A0116Z-044910 1 778.000 916.200 2511-0296044900 2 2 .5 .5 02/10/2009 -498.719
8773 10A0116Z-044910 1 778.000 916.200 2511-0296044900 2 2 .5 .5 02/10/2009 500.000
When only want two rows ...
8773 10A0116Z-044910 1 778.000 916.200 2511-0296044900 2 2 .5 .5 02/10/2009 -498.719
8773 10A0116Z-044910 1 778.000 916.200 2511-0296044900 2 2 .5 .5 02/10/2009 500.000
Would really like one row (sum of last column)
8773 10A0116Z-044910 1 778.000 916.200 2511-0296044900 2 2 .5 .5 02/10/2009 1.281
Any suggestions will be greatly appreciated 🙂
Thanks
Dedra
SELECT
cont.conid as "ContainerID"
,itmMP.itmItemNumber as "item"
,cont.conQuantity as "Qty"
,itmdimMPCut.dimY AS "Length"
,itmdimMPCut.dimX AS "Width"
,itm.itmitemnumber as "Material"
--,prd.prdid as "Prod Order ID"
,max(case when itmedge.itmeEdgeNo = 1 then itmedge.itmethickness end) as Edge1
,max(case when itmedge.itmeEdgeNo = 3 then itmedge.itmethickness end) as Edge3
,max(case when itmedge.itmeEdgeNo = 2 then itmedge.itmethickness end) as Edge2
,max(case when itmedge.itmeEdgeNo = 4 then itmedge.itmethickness end) as Edge4
,MAX (CONVERT (VarChar (20), cont.conDate , 101 )) as "Cut Date"
,itmloc.islLocationQty as QOH
FROM
ALG.productionorders AS alg
JOIN dbo.ProductionOrders AS prd
ON alg.prdID = prd.prdID
left JOIN dbo.ProductionOrderItems AS prdi
ON prd.prdID = prdi.prdID
left JOIN dbo.OrderItems AS oriMP
ON prdi.olnID = oriMP.olnID
left JOIN dbo.Items AS itmMP
ON oriMP.itmID = itmMP.itmID
left JOIN dbo.ItemDimensions AS itmdimMPCut
ON itmMP.itmID = itmdimMPCut.itmID AND itmdimMPCut.dimID = 3
left JOIN dbo.ItemDimensions AS itmdimMPMachined
ON itmMP.itmID = itmdimMPMachined.itmID AND itmdimMPMachined.dimID = 2
left JOIN dbo.ItemDimensions AS itmdimMPFinished
ON itmMP.itmID = itmdimMPFinished.itmID AND itmdimMPFinished.dimID = 1
left JOIN dbo.OrderItems AS oriMA
ON oriMP.itmID = oriMA.itmID
left JOIN dbo.Items AS itmMA
ON oriMA.itmID = itmMA.itmID
--LEFT JOIN dbo.ItemCNCGeometry AS itmCNCGeo ON itmMP.itmID = itmCNCGeo.itmID AND itmCNCGeo.itmgCNCProgIndex =1
--LEFT JOIN dbo.OrderItemCNCPrograms AS oriCNCPro ON itmMP.itmID = oriCNCPro.itmID AND itmCNCGeo.itmgCNCProgIndex =1
LEFT JOIN dbo.OrderItemMaterials orimat
ON oriMP.itmID = orimat.itmID
AND oriMP.itmIDInstance = orimat.itmIDInstance
AND oriMP.olnID = orimat.olnID
AND oriMP.olnIDInstance = orimat.olnIDInstance
LEFT JOIN dbo.Materials mat
ON orimat.TopSurCode = mat.TopSurCode
AND orimat.BotSurCode = mat.BotSurCode
AND orimat.CorCode = mat.CorCode
LEFT JOIN [dbo].[MaterialSourceItems] itmm
ON itmm.corCode = Mat.corCode
AND itmm.TopSurCode = Mat.TopSurCode
AND itmm.BotSurCode = Mat.BotSurCode
LEFT JOIN dbo.Items itm on itm.itmID = itmm.itmID
left join dbo.itemcontainer itmcont
on oriMP.itmid = itmcont.itmid
left join dbo.container as cont
on itmcont.conid = cont.conid
left join itemedges itmedge
on orimp.itmid = itmedge.itmid
join ItemStockingLocations itmloc
on itm.itmid = itmloc.itmid
where
oriMP.dlCode = 'MP' and
prd.sttid = 4
GROUP BYcont.conid
,itm.itmitemnumber
,itmMP.itmItemNumber
,itmMP.itmDescription2
,oriMP.oriReqQty
,itmdimMPCut.dimY
,itmdimMPCut.dimX
--,itmdimMPCut.dimZ
--,prd.prdProdDate
--,prd.prdProdBatch
,prd.prdid
,cont.conquantity
,cont.conDate
,itmloc.islLocationQty
ORDER BY
cont.conid

Viewing 3 posts - 1 through 2 (of 2 total)

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

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 1

I think you just got too many fields listed in group by ...

Try ...

[font="Courier New"] SELECT cont.conid AS "ContainerID"

,itmMP.itmItemNumber AS "item"

,cont.conQuantity AS "Qty"

,itmdimMPCut.dimY AS "Length"

,itmdimMPCut.dimX AS "Width"

,itm.itmitemnumber AS "Material"

,MAX(CASE WHEN itmedge.itmeEdgeNo = 1 THEN itmedge.itmethickness END) AS Edge1

,MAX(CASE WHEN itmedge.itmeEdgeNo = 3 THEN itmedge.itmethickness END) AS Edge3

,MAX(CASE WHEN itmedge.itmeEdgeNo = 2 THEN itmedge.itmethickness END) AS Edge2

,MAX(CASE WHEN itmedge.itmeEdgeNo = 4 THEN itmedge.itmethickness END) AS Edge4

,MAX (CONVERT (VARCHAR (20), cont.conDate , 101 )) AS "Cut Date"

,SUM(itmloc.islLocationQty) AS QOH

FROM ALG.productionorders AS alg

JOIN dbo.ProductionOrders AS prd ON alg.prdID = prd.prdID

LEFT JOIN dbo.ProductionOrderItems AS prdi ON prd.prdID = prdi.prdID

LEFT JOIN dbo.OrderItems AS oriMP ON prdi.olnID = oriMP.olnID

LEFT JOIN dbo.Items AS itmMP ON oriMP.itmID = itmMP.itmID

LEFT JOIN dbo.ItemDimensions AS itmdimMPCut ON itmMP.itmID = itmdimMPCut.itmID

AND itmdimMPCut.dimID = 3

LEFT JOIN dbo.ItemDimensions AS itmdimMPMachined ON itmMP.itmID = itmdimMPMachined.itmID

AND itmdimMPMachined.dimID = 2

LEFT JOIN dbo.ItemDimensions AS itmdimMPFinished ON itmMP.itmID = itmdimMPFinished.itmID

AND itmdimMPFinished.dimID = 1

LEFT JOIN dbo.OrderItems AS oriMA ON oriMP.itmID = oriMA.itmID

LEFT JOIN dbo.Items AS itmMA ON oriMA.itmID = itmMA.itmID

LEFT JOIN dbo.OrderItemMaterials orimat ON oriMP.itmID = orimat.itmID

AND oriMP.itmIDInstance = orimat.itmIDInstance

AND oriMP.olnID = orimat.olnID

AND oriMP.olnIDInstance = orimat.olnIDInstance

LEFT JOIN dbo.Materials mat ON orimat.TopSurCode = mat.TopSurCode

AND orimat.BotSurCode = mat.BotSurCode

AND orimat.CorCode = mat.CorCode

LEFT JOIN [dbo].[MaterialSourceItems] itmm ON itmm.corCode = Mat.corCode

AND itmm.TopSurCode = Mat.TopSurCode

AND itmm.BotSurCode = Mat.BotSurCode

LEFT JOIN dbo.Items itm ON itm.itmID = itmm.itmID

LEFT JOIN dbo.itemcontainer itmcont ON oriMP.itmid = itmcont.itmid

LEFT JOIN dbo.container AS cont ON itmcont.conid = cont.conid

LEFT JOIN itemedges itmedge ON orimp.itmid = itmedge.itmid

JOIN ItemStockingLocations itmloc ON itm.itmid = itmloc.itmid

WHERE oriMP.dlCode = 'MP'

AND prd.sttid = 4

GROUP BY cont.conid

,itmMP.itmItemNumber

,cont.conQuantity

,itmdimMPCut.dimY

,itmdimMPCut.dimX

,itm.itmitemnumber

ORDER BY cont.conid

[/font]

[font="Arial"]---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

How to ask for help .. Read Best Practices here[/url].

steve dassin SSCrazy Points: 2396 More actions · Answer 2

And true-believers recoil at the thought of a cursor. 🙂

www.beyondsql.blogspot.com