Sum Help

Question

Sum Help

kat35601

SSC Eights!

Points: 825
More actions
May 12, 2015 at 3:29 pm

#296954

I need to sum box count the trouble there are multiple rows for each item or items in a box for Line ID(LID) this is one table in a large query which I have included below.There can be thousands of rows.
LID 1 is 2 boxes
LID 2 is 1 boxes
LID 3 is 1 box
LID 4 is 1 box
LID 5 is 3 boxes
example table
LID BoxCount
1 2
1 2
1 2
1 2
2 1
2 1
3 1
4 1
4 1
5 3
5 3
5 3
SELECT *, CAST(jmapartlongdescriptiontext as varchar(50)) as jmapartlongdescriptiontext22
FROM SalesOrders INNER JOIN
SalesOrderLines ON SalesOrders.ompSalesOrderID = SalesOrderLines.omlSalesOrderID INNER JOIN
SalesOrderDeliveries ON SalesOrderLines.omlSalesOrderID = SalesOrderDeliveries.omdSalesOrderID AND
SalesOrderLines.omlSalesOrderLineID = SalesOrderDeliveries.omdSalesOrderLineID INNER JOIN
SalesOrderJobLinks ON SalesOrderDeliveries.omdSalesOrderID = SalesOrderJobLinks.omjSalesOrderID AND
SalesOrderDeliveries.omdSalesOrderLineID = SalesOrderJobLinks.omjSalesOrderLineID AND
SalesOrderDeliveries.omdSalesOrderDeliveryID = SalesOrderJobLinks.omjSalesOrderDeliveryID INNER JOIN
Jobs ON SalesOrderJobLinks.omjJobID = Jobs.jmpJobID INNER JOIN
UPAINTSORT On SalesOrderLines.UOMLPaintSort = UPAINTSORT.UPSSortID INNER JOIN
(Select * from JobAssemblies where jmaLevel <> 1 and (left(jmaPartID, 3) = 'HM_' or left(jmaPartID, 3) = 'OK_' or left(jmaPartID, 3) = 'MDF'
or left(jmaPartID, 3) = 'CH_' or left(jmaPartID, 3) = 'SG_')) as JobAssemblies ON Jobs.jmpJobID = JobAssemblies.jmaJobID
WHERE UOMPSCHEDULENUMBER='052215-01' and ompClosed = '0'

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

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

Andrew P SSCarpal Tunnel Points: 4649 More actions · Answer 1

I would do something like below, but you will need to specify all fields listed in your SELECT statement except LID as part of a GROUP BY clause. Your SELECT * is likely to cause maintenance or performance issues - I suggest explicitly stating the columns you want returned, particularly when joining to so many tables.

sum(case

when LID = 1 then 2

when LID in (2,3,4) then 1

when LID = 5 then 3

else NULL /* You could skip this line, as the default is NULL, but consider what would happen if there were to be a LID value of <0 or >5.

end)

A possible implementation:

SELECT CAST(SalesOrderLines.jmapartlongdescriptiontext as varchar(50)) as jmapartlongdescriptiontext22

, sum(

case

when LID = 1 then 2

when LID in (2,3,4) then 1

when LID = 5 then 3

else NULL

end) as box_count

FROM SalesOrders INNER JOIN

SalesOrderLines ON SalesOrders.ompSalesOrderID = SalesOrderLines.omlSalesOrderID INNER JOIN

SalesOrderDeliveries ON SalesOrderLines.omlSalesOrderID = SalesOrderDeliveries.omdSalesOrderID AND

SalesOrderLines.omlSalesOrderLineID = SalesOrderDeliveries.omdSalesOrderLineID INNER JOIN

SalesOrderJobLinks ON SalesOrderDeliveries.omdSalesOrderID = SalesOrderJobLinks.omjSalesOrderID AND

SalesOrderDeliveries.omdSalesOrderLineID = SalesOrderJobLinks.omjSalesOrderLineID AND

SalesOrderDeliveries.omdSalesOrderDeliveryID = SalesOrderJobLinks.omjSalesOrderDeliveryID INNER JOIN

Jobs ON SalesOrderJobLinks.omjJobID = Jobs.jmpJobID INNER JOIN

UPAINTSORT On SalesOrderLines.UOMLPaintSort = UPAINTSORT.UPSSortID INNER JOIN

(Select * from JobAssemblies where jmaLevel <> 1 and (left(jmaPartID, 3) = 'HM_' or left(jmaPartID, 3) = 'OK_' or left(jmaPartID, 3) = 'MDF'

or left(jmaPartID, 3) = 'CH_' or left(jmaPartID, 3) = 'SG_')) as JobAssemblies ON Jobs.jmpJobID = JobAssemblies.jmaJobID

WHERE UOMPSCHEDULENUMBER='052215-01' and ompClosed = '0'

GROUP BY CAST(SalesOrderLines.jmapartlongdescriptiontext as varchar(50))

Dwain Camps SSC Guru Points: 86948 More actions · Answer 2

WITH example (LID, BoxCount) AS

(

SELECT 1, 2

UNION ALL SELECT 1, 2

UNION ALL SELECT 2, 1

UNION ALL SELECT 3, 1

UNION ALL SELECT 4, 1

UNION ALL SELECT 5, 3

)

SELECT LID, BoxCount=MAX(BoxCount)

FROM example

GROUP BY LID;

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

I have nothing to add to this post except to say that I love the title, "Sum Help". It would be even better if the title was, "I need sum help." :hehe:

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001