There isn't a prebuild function for finding the product of field or set of values in SQL Server. But there is a workaround by using a combination of the SUM(), POWER() and LOG10() functions. If not for this query we would have to fetch all the records to the front-end and then do the calculation there or use cursors to parse the records and write the logic for multiplying within that procedure. I was doing it this way in some of my previous projects so this online query was really useful for me and my team -- hopefully it will be useful to you too. It works on SQL Server 7 and 2000.
Follow these steps:
- Find the LOG value of all the data
- Add all the LOG values
- With the help of POWER function return the SUM to the POWER 10.
SELECT POWER(10, SUM(LOG10(SampleField))) from SampleTable
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free SQL Server tips and scripts.
- Tip contest: Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our SQL, database design, SQL Server, DB2, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical SQL Server questions--or help out your peers by answering them--in our active forums.
- Best Web Links: SQL Server tips, tutorials, and scripts from around the Web.