multiply aggregate

Question

Post reply

multiply aggregate

spin

Hall of Fame

Points: 3271
More actions
August 15, 2011 at 4:22 am

#255660

hi

i have three rows in a table for each product. each line represent a different unit of measure value.

so

row 1 = 1 (qty in units)

row 2 = 6 (units in a case)

row 3 = 1020 (cases to a pallet)

i need to get the total number of units to a pallet. The best way i can think to do this is to multiply the value in each line, i.e. 1x6x1020 = 6120 units to a pallet). Is there an aggregate i can use for this like SUM() that will multiply the lines?? any suggestions on how to get the total value?

Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Mark Cowne One Orange Chip Points: 26865 More actions · Answer 1

You can sum the logs. A SQLCLR aggregate would work as well.

DECLARE @t TABLE(Row INT, Qty INT)



INSERT INTO @t(Row,Qty)

SELECT 1,1 UNION ALL

SELECT 2,6 UNION ALL

SELECT 3,1020

SELECT EXP(SUM(LOG(Qty)))

FROM @t

You'll have to handle zero quantities yourself here, otherwise you'll get an error.

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Or like this, depending on your data:

SELECT r1.ProductID,



	[total number of units to a pallet] = r1.[qty in units] * r2.[units in a case] * r3.[cases to a pallet]

FROM Products r1

INNER JOIN Products r2

	ON r2.ProductID = r1.ProductID AND r2.RowType = 'units in a case'

INNER JOIN Products r3

	ON r3.ProductID = r1.ProductID AND r3.RowType = 'cases to a pallet'

WHERE r1.RowType = 'qty in units'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

Simple answer to your question is: NO - such aggregate function does not exist. There is a rationale behind it I guess. Such function would easely cause overflow.

You can try use EXP(SUM(LOG(Column))), but I'm not sure how it will work for large datasets.

Also, there is an option to write user defined aggregate CLR function.

BUT, wait untill JC will see your post...:w00t: You will then know that your table is not a table 😀

It does look like crap design to me. You should really consider to turn your rows to columns...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Phil Parkin SSC Guru Points: 245813 More actions · Answer 4

BUT, wait untill JC will see your post... [w00t] You will then know that your table is not a table [BigGrin]

Wow, Jesus monitors this forum too?

:hehe:

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

Phil Parkin (8/15/2011)

BUT, wait untill JC will see your post... [w00t] You will then know that your table is not a table [BigGrin]

Wow, Jesus monitors this forum too?

:hehe:

Joe Celko advertises his books here almost like testaments, so take it is as you wish but I'm atheist in this regard :hehe:

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

spin Hall of Fame Points: 3271 More actions · Answer 6

spin

Hall of Fame

Points: 3271

August 15, 2011 at 8:35 am

#1368643

thanks all (special thanks to the messiah),

yes i know the design is crap. it's from an old proteus db which it causing me serious headaches.

i've finally had to create a pivot table which i can split into 3 columns and multiply from there. by no means a desirable result but one that works.