SQL Calculation

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » SQL Calculation

11 posts, Page 1 of 2

1 2 »»

SQL Calculation

Rate Topic

Display Mode

Topic Options

Author

Message

scottsanpedro

Posted Tuesday, November 13, 2012 8:07 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, May 10, 2013 3:24 AM
Points: 83, Visits: 277

Hi all. I hope you can lend a hand to my sore head!
I have to write a query to work out a running multiplication by a factor! ouch.

Start number = 12. Call that x

ID Factor
1 1.1
2 1.1
1 1.1
2 1.2

So once group the answer should be
ID Result
1 14.52 ((12 * 1.1) = 13.2 then (13.2 * 1.1) = 14.51
2 15.84 ((12 * 1.1) = 13.2 then (13.2 * 1.2) = 15.84

I have been looking at http://mangalpardeshi.blogspot.co.uk/2009/06/multiplying-column-values.html but this just adds the multiplied field onto each other.
I will keep trying in the meantime. Thanks for any pointers. Scott

Post #1384117

Jason-299789

Posted Tuesday, November 13, 2012 8:57 AM

SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 1:30 AM
Points: 803, Visits: 2,124

post removed, Better solution below......

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Post #1384135

John Mitchell-245523

Posted Tuesday, November 13, 2012 9:00 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:44 AM
Points: 4,434, Visits: 7,218

Or use logarithms:

CREATE TABLE #john (ID int, Factor decimal(5,3))
INSERT INTO #john VALUES 
(1,1.1),
(2,1.1),
(1,1.1),
(2,1.2)
SELECT * FROM #john

DECLARE @x decimal(5,3)
SET @x = 12

SELECT
	ID
,	EXP(SUM(LOG(Factor))) * @x  AS Answer
FROM
	#john
GROUP BY
	ID

John

Post #1384136

Jason-299789

Posted Tuesday, November 13, 2012 9:01 AM

SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 1:30 AM
Points: 803, Visits: 2,124

John Mitchell-245523 (11/13/2012)

Or use logarithms:

CREATE TABLE #john (ID int, Factor decimal(5,3))
INSERT INTO #john VALUES 
(1,1.1),
(2,1.1),
(1,1.1),
(2,1.2)
SELECT * FROM #john

DECLARE @x decimal(5,3)
SET @x = 12

SELECT
	ID
,	EXP(SUM(LOG(Factor))) * @x  AS Answer
FROM
	#john
GROUP BY
	ID

John

+1,

Nice use of the EXP and LOG functions that I'd forgotten all about.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Post #1384139

scottsanpedro

Posted Tuesday, November 13, 2012 9:13 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, May 10, 2013 3:24 AM
Points: 83, Visits: 277

Thanks so much. Works perfectly. Scott

Post #1384148

CELKO

Posted Tuesday, November 13, 2012 10:20 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782

If this were a ANSI built-in the name would be PRD(), followign the pattern for SUM(), AVG() etc. Here is a version of the aggregate product function in SQL. You will need to have the logarithm and exponential functions.

The idea is that there are three special cases - all positive numbers, one or more zeros, and some negative numbers in the set.

You can find out what your situation is with a quick test on the sign() of the minimum value in the set.

Within the case where you have negative numbers, there are two sub-cases: (1) an even number of negatives or (2) an odd number of negatives. You then need to apply some High School algebra to determine the sign of the final result.

SELECT CASE MIN (SIGN(nbr))
WHEN 1 THEN EXP(SUM(LN(nbr))) -- all positive numbers
WHEN 0 THEN 0.00 -- some zeroes
WHEN -1 -- some negative numbers
THEN (EXP(SUM(LN(ABS(nbr))))
* (CASE WHEN
MOD (SUM(ABS(SIGN(nbr)-1)/ 2)), 2) = 1
THEN -1.00 ELSE 1.00 END)
ELSE NULL END AS big_pi
FROM Foobar;

SELECT CASE MIN(ABS(SIGN(nbr)))
WHEN 0 THEN 0.00 -- some zeroes
ELSE -- no zeroes
EXP(SUM(LOG(ABS(NULLIF(nbr, 0)))))
* CASE WHEN MOD (CAST(SUM(ABS(SIGN(nbr)-1)/2) AS INTEGER), 2) = 1
THEN -1.00 ELSE 1.00 END
END AS big_pi

As an aside, the book BYPASSES:A SIMPLE APPROACH TO COMPLEXITY by Z. A. Melzak (Wiley-Interscience, 1983, ISBN 0-471-86854-X), is short mathematical book on the general principle of conjugacy. This is the method of using a transform and its inverse to reduce the complexity of a calculation.

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Post #1384183

scottsanpedro

Posted Tuesday, November 13, 2012 11:24 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, May 10, 2013 3:24 AM
Points: 83, Visits: 277

Thanks for the additional information. It's helpful to receive and be able to look further into the process.
Many thanks scott.

Post #1384213

scottsanpedro

Posted Thursday, February 07, 2013 5:37 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, May 10, 2013 3:24 AM
Points: 83, Visits: 277

This function has been working 100% in production for some time. Now I need to factor down as well i.e. 0.9
Currently this actually makes the result 0.
I will be looking into this and report back once I have a solution.
Scott

Post #1416995

Sergiy

Posted Thursday, February 07, 2013 4:41 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:38 PM
Points: 4,557, Visits: 8,213

It does not do it for me.
Can you post your actual data to use with the test script above?

Post #1417388

scottsanpedro

Posted Friday, February 08, 2013 1:37 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, May 10, 2013 3:24 AM
Points: 83, Visits: 277

I had one of those thoughts dropping off last night. I think maybe it might be due to the datatype.
I will try it out and if not then post my code.
Thank for your input. Scott

Post #1417505

« Prev Topic | Next Topic »

11 posts, Page 1 of 2

1 2 »»

Permissions