LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Forum Statistics

Users
Members:
1879
Members Online:
3
Guests Online:
45

Total Post History
Posts:
81451
Topics:
18716

7-Day Post History
New Posts:
0
New Topics:
0
Active Topics:
0

Our newest member
mwojcik

Other

FAQ
All times are UTC [ DST ]

Product Aggregate (SQL)

Mind Boggling Puzzles, to keep that grey matter in shape...
Forum rules
Always post answers in a "Hidecode" tag, so that others have a chance to answer the question too.
Please wait...

Product Aggregate (SQL)

Postby AlexCuse on Sun Feb 14, 2010 3:52 am

I saw a good trick for this the other day, wanted to see if anyone else was familiar. The desired result is the product of all values for a column.

So using this data set:

  1. DECLARE @T TABLE (Id INT, VALUE BIGINT)
  2.  
  3. INSERT @T
  4. SELECT 1, 5
  5. UNION ALL SELECT 1, 7
  6. UNION ALL SELECT 2, 15
  7. UNION ALL SELECT 3, 9
  8. UNION ALL SELECT 3, 154
  9. UNION ALL SELECT 3, 1
  10. UNION ALL SELECT 3, 14
  11. UNION ALL SELECT 4, 195
  12. UNION ALL SELECT 4, 12


We'd want to see this result:

Id Product
1 35
2 15
3 19404
4 2340

I'd be interested in seeing solutions for any database. Don't worry about exceeding data type size limits or anything (not that you would with this dataset).
Say what you like about the tenets of National Socialism Dude, at least it's an ethos
User avatar
AlexCuse
LTD Admin
LTD Admin
LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031
LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031
LTD Gold - Rating: 1031
 
Posts: 5523
Joined: Tue Oct 09, 2007 5:26 pm
Location: Pennsylvania, US
Unrated

Re: Product Aggregate (SQL)

Postby Naomi on Sun Feb 14, 2010 6:21 am

Here is my solution.

Code is hidden, SHOW
Last edited by Naomi on Mon Feb 15, 2010 5:42 pm, edited 3 times in total.
Premature optimization is the root of all evil in programming.
Donald Knuth, repeating C. A. R. Hoare
Naomi
Senior Sage
Senior Sage
LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234
 
Posts: 1754
Joined: Thu Mar 26, 2009 5:46 am
Location: WI

Re: Product Aggregate (SQL)

Postby ramireddyindia on Sun Feb 14, 2010 9:53 am

Code is hidden, SHOW


I know this technique... theoretically its correct...But i have some doubt about this... It will give some wrong value for some.....
ramireddyindia
Sage
Sage
LTD Bronze - Rating: 146LTD Bronze - Rating: 146LTD Bronze - Rating: 146
 
Posts: 267
Joined: Tue Jul 14, 2009 1:45 pm

Re: Product Aggregate (SQL)

Postby Naomi on Sun Feb 14, 2010 4:03 pm

Hi Rami,

Yes, Brad Schulz gave the same answer here

http://social.msdn.microsoft.com/Forums ... 2dce8863c8

I spent at least half an hour yesterday solving it with the ordered update technique.
Premature optimization is the root of all evil in programming.
Donald Knuth, repeating C. A. R. Hoare
Naomi
Senior Sage
Senior Sage
LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234
 
Posts: 1754
Joined: Thu Mar 26, 2009 5:46 am
Location: WI
Unrated

Re: Product Aggregate (SQL)

Postby Naomi on Sun Feb 14, 2010 4:31 pm

The limitation of this method is you can not have 0 among the values or negative integers. It only works for positive integers, while my method works for any number.
Premature optimization is the root of all evil in programming.
Donald Knuth, repeating C. A. R. Hoare
Naomi
Senior Sage
Senior Sage
LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234
 
Posts: 1754
Joined: Thu Mar 26, 2009 5:46 am
Location: WI
Unrated

Re: Product Aggregate (SQL)

Postby ramireddyindia on Mon Feb 15, 2010 6:32 am

Code is hidden, SHOW
ramireddyindia
Sage
Sage
LTD Bronze - Rating: 146LTD Bronze - Rating: 146LTD Bronze - Rating: 146
 
Posts: 267
Joined: Tue Jul 14, 2009 1:45 pm

Re: Product Aggregate (SQL)

Postby Thirster42 on Mon Feb 15, 2010 3:50 pm

tadaa!

Code is hidden, SHOW


not the best solution, and if you add more values to the id's it won't work, but it works for the dataset given.
Ask three DBAs a question, and you’ll get four different answers.

-Brent Ozar
User avatar
Thirster42
Guru
Guru
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
 
Posts: 4646
Joined: Tue Nov 11, 2008 5:49 pm
Location: Jefferson City, Mo
Unrated

Re: Product Aggregate (SQL)

Postby Thirster42 on Mon Feb 15, 2010 3:51 pm

Naomi wrote:Here is my solution.

Code is hidden, SHOW


next time maybe put in in the tsql code? ;)
Ask three DBAs a question, and you’ll get four different answers.

-Brent Ozar
User avatar
Thirster42
Guru
Guru
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
 
Posts: 4646
Joined: Tue Nov 11, 2008 5:49 pm
Location: Jefferson City, Mo
Unrated

Re: Product Aggregate (SQL)

Postby Naomi on Mon Feb 15, 2010 5:39 pm

Definitely not the best solution :)

Thirster42 wrote:tadaa!

Code is hidden, SHOW


not the best solution, and if you add more values to the id's it won't work, but it works for the dataset given.
Premature optimization is the root of all evil in programming.
Donald Knuth, repeating C. A. R. Hoare
Naomi
Senior Sage
Senior Sage
LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234
 
Posts: 1754
Joined: Thu Mar 26, 2009 5:46 am
Location: WI
Unrated

Re: Product Aggregate (SQL)

Postby Naomi on Mon Feb 15, 2010 5:43 pm

Thirster42 wrote:
Naomi wrote:Here is my solution.

Code is hidden, SHOW


next time maybe put in in the tsql code? ;)


:)) Done.
Premature optimization is the root of all evil in programming.
Donald Knuth, repeating C. A. R. Hoare
Naomi
Senior Sage
Senior Sage
LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234
 
Posts: 1754
Joined: Thu Mar 26, 2009 5:46 am
Location: WI
Unrated

Re: Product Aggregate (SQL)

Postby Emtucifor on Tue Feb 16, 2010 7:20 pm

I used the product() aggregate in a thread in LTD not too long ago: Topic9044: Set Based Approach Query for this problem
God cries a little bit every time someone builds a database.
User avatar
Emtucifor
Guru
Guru
LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033
LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033
LTD Gold - Rating: 1033
 
Posts: 2835
Joined: Fri May 30, 2008 9:30 pm
Location: Bellingham, WA
Unrated

Re: Product Aggregate (SQL)

Postby Thirster42 on Tue Feb 16, 2010 7:59 pm

recursive cte, not sure if anyone actually did this yet. took me about an hour to figure out the recursive logic.

Code is hidden, SHOW
Ask three DBAs a question, and you’ll get four different answers.

-Brent Ozar
User avatar
Thirster42
Guru
Guru
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
 
Posts: 4646
Joined: Tue Nov 11, 2008 5:49 pm
Location: Jefferson City, Mo
Unrated

Re: Product Aggregate (SQL)

Postby Emtucifor on Tue Feb 16, 2010 8:27 pm

My version of a recursive CTE for this:

Code is hidden, SHOW
God cries a little bit every time someone builds a database.
User avatar
Emtucifor
Guru
Guru
LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033
LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033
LTD Gold - Rating: 1033
 
Posts: 2835
Joined: Fri May 30, 2008 9:30 pm
Location: Bellingham, WA

Re: Product Aggregate (SQL)

Postby Thirster42 on Tue Feb 16, 2010 8:52 pm

Emtucifor wrote:My version of a recursive CTE for this:

Code is hidden, SHOW


quit copying me and making better versions!
Ask three DBAs a question, and you’ll get four different answers.

-Brent Ozar
User avatar
Thirster42
Guru
Guru
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
 
Posts: 4646
Joined: Tue Nov 11, 2008 5:49 pm
Location: Jefferson City, Mo
Unrated

Re: Product Aggregate (SQL)

Postby Emtucifor on Tue Feb 16, 2010 9:05 pm

I actually didn't name my CTE DATA after yours, it's just a CTE name I like... :lol:
God cries a little bit every time someone builds a database.
User avatar
Emtucifor
Guru
Guru
LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033
LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033
LTD Gold - Rating: 1033
 
Posts: 2835
Joined: Fri May 30, 2008 9:30 pm
Location: Bellingham, WA
Unrated