February 6, 2012 at 10:41 am
2 type of House
4 type of Colour
3 type of Size
5 type of Flavour
2*4*3*5 = 120 combinations
though how they are related beats me 😀
edit typo
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 6, 2012 at 10:43 am
though how they are related beats me 😀
LOL They aren't, its just replica data of actual data I'm working with 🙂
February 6, 2012 at 10:48 am
J Livingston SQL (2/6/2012)
2 type of House4 type of Colour
3 type of Size
5 type of Flavour
2*4*3*5 = 120 combinations
though how they are related beats me 😀
edit typo
Except that for group 2 and 4 they can have any number of choices. Group 2 can have 1, 2, 3 or 4 of the options.
I took it like group 1 and group 3 are radio buttons (only 1 option) and group 2 is 4 checkboxes (with at least 1 required). Of course getting the number of possibilities is still pretty simple. No clue how to get the actual possibilities in sql which is what the OP is looking for.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 6, 2012 at 10:49 am
Phil Parkin (2/6/2012)
Certainly can't put together some sort of sql that can think
Come on Sean, don't let the side down, that should be easy for you :w00t:
LOL. I will start working on that then. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 6, 2012 at 10:56 am
Sean Lange (2/6/2012)
J Livingston SQL (2/6/2012)
2 type of House4 type of Colour
3 type of Size
5 type of Flavour
2*4*3*5 = 120 combinations
though how they are related beats me 😀
edit typo
Except that for group 2 and 4 they can have any number of choices. Group 2 can have 1, 2, 3 or 4 of the options.
I took it like group 1 and group 3 are radio buttons (only 1 option) and group 2 is 4 checkboxes (with at least 1 required). Of course getting the number of possibilities is still pretty simple. No clue how to get the actual possibilities in sql which is what the OP is looking for.
That is exactly what this data is coming from...radio buttons and checkboxes 🙂 I'm coming up with approx 2,100 variations based on the 4 CatIds I have listed...I may have missed a few though.
February 6, 2012 at 11:17 am
mmiller 85218 (2/6/2012)
Sean Lange (2/6/2012)
J Livingston SQL (2/6/2012)
2 type of House4 type of Colour
3 type of Size
5 type of Flavour
2*4*3*5 = 120 combinations
though how they are related beats me 😀
edit typo
Except that for group 2 and 4 they can have any number of choices. Group 2 can have 1, 2, 3 or 4 of the options.
I took it like group 1 and group 3 are radio buttons (only 1 option) and group 2 is 4 checkboxes (with at least 1 required). Of course getting the number of possibilities is still pretty simple. No clue how to get the actual possibilities in sql which is what the OP is looking for.
That is exactly what this data is coming from...radio buttons and checkboxes 🙂 I'm coming up with approx 2,100 variations based on the 4 CatIds I have listed...I may have missed a few though.
hmmm...my maths may well be totally wrong (long time since I did combinations and permutations)...so I am going to throw this in and expect to crash and burn 😉
2 type of House only one choice
4 type of Colour one or many
3 type of Size only one choice
5 type of Flavour one or many
2 * (4!) * 3 * (5!)
2 * 24 * 3 * 120
=17280
Hopefully some learned person will enlighten me.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 6, 2012 at 12:20 pm
J Livingston SQL (2/6/2012)
mmiller 85218 (2/6/2012)
Sean Lange (2/6/2012)
J Livingston SQL (2/6/2012)
2 type of House4 type of Colour
3 type of Size
5 type of Flavour
2*4*3*5 = 120 combinations
though how they are related beats me 😀
edit typo
Except that for group 2 and 4 they can have any number of choices. Group 2 can have 1, 2, 3 or 4 of the options.
I took it like group 1 and group 3 are radio buttons (only 1 option) and group 2 is 4 checkboxes (with at least 1 required). Of course getting the number of possibilities is still pretty simple. No clue how to get the actual possibilities in sql which is what the OP is looking for.
That is exactly what this data is coming from...radio buttons and checkboxes 🙂 I'm coming up with approx 2,100 variations based on the 4 CatIds I have listed...I may have missed a few though.
hmmm...my maths may well be totally wrong (long time since I did combinations and permutations)...so I am going to throw this in and expect to crash and burn 😉
2 type of House only one choice
4 type of Colour one or many
3 type of Size only one choice
5 type of Flavour one or many
2 * (4!) * 3 * (5!)
2 * 24 * 3 * 120
=17280
Hopefully some learned person will enlighten me.
This makes sense to me....now just need to understand how to apply it to actual sql code 🙂
February 6, 2012 at 12:26 pm
Like I said getting the actual numbers is fairly straight forward, getting that data out of sql is a whole new beast.
It has been a long time since I was current on the math behind that stuff too which is why I found the sites I linked previously. I don't think it is a straight factorial though, but that is not nearly as important as figuring out your sql.
I still am curious why you need to have a dataset with all possibilities.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 6, 2012 at 12:30 pm
I still am curious why you need to have a dataset with all possibilities.
Well, they want a count of all total possibilities...with that, i can apply the factoral mathematics and not have to code for it.
But in addition, there is a form filled out with 16 different Categories and anywhere from 2 to 13 options to select. They fill out a form with checkboxes and radio buttons. They want to understand how many distinct combinations we have on our programs.
The purpose? I have no idea...I was just tasked 🙂
February 6, 2012 at 12:40 pm
mmiller 85218 (2/6/2012)
J Livingston SQL (2/6/2012)
mmiller 85218 (2/6/2012)
Sean Lange (2/6/2012)
J Livingston SQL (2/6/2012)
2 type of House4 type of Colour
3 type of Size
5 type of Flavour
2*4*3*5 = 120 combinations
though how they are related beats me 😀
edit typo
Except that for group 2 and 4 they can have any number of choices. Group 2 can have 1, 2, 3 or 4 of the options.
I took it like group 1 and group 3 are radio buttons (only 1 option) and group 2 is 4 checkboxes (with at least 1 required). Of course getting the number of possibilities is still pretty simple. No clue how to get the actual possibilities in sql which is what the OP is looking for.
That is exactly what this data is coming from...radio buttons and checkboxes 🙂 I'm coming up with approx 2,100 variations based on the 4 CatIds I have listed...I may have missed a few though.
hmmm...my maths may well be totally wrong (long time since I did combinations and permutations)...so I am going to throw this in and expect to crash and burn 😉
2 type of House only one choice
4 type of Colour one or many
3 type of Size only one choice
5 type of Flavour one or many
2 * (4!) * 3 * (5!)
2 * 24 * 3 * 120
=17280
Hopefully some learned person will enlighten me.
This makes sense to me....now just need to understand how to apply it to actual sql code 🙂
I think there's a small issue with the math. Using factorial would work for permutations (i.e. the orders of the colors matter), but is not right for combinations. So if the order in which the colors are specified is NOT important,
To know all non-repeating combinations of length d withing a distinct population of n you're looking for n!/((n-d)!d!)
To know all non-repeating combinations of all lengths within a distinct population of n you're looking for (2^n)-1
So for four colors it is (2^4) - 1 -= 15, 5 flavors yields (2^5)-1 = 31, so total combinations would be:
2*15*3*31 = 2790
For more info:
http://www.mathsisfun.com/combinatorics/combinations-permutations.html
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 6, 2012 at 12:44 pm
mmiller 85218 (2/6/2012)
I still am curious why you need to have a dataset with all possibilities.
Well, they want a count of all total possibilities...with that, i can apply the factoral mathematics and not have to code for it.
But in addition, there is a form filled out with 16 different Categories and anywhere from 2 to 13 options to select. They fill out a form with checkboxes and radio buttons. They want to understand how many distinct combinations we have on our programs.
The purpose? I have no idea...I was just tasked 🙂
Again it sounds like you only want to number of possibilities but at other times you said you want a resultset with details about what they are. Looks like Matt came along and corrected both of us on calculations. Does that get you what you need?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 6, 2012 at 12:47 pm
Sean Lange (2/6/2012)
mmiller 85218 (2/6/2012)
I still am curious why you need to have a dataset with all possibilities.
Well, they want a count of all total possibilities...with that, i can apply the factoral mathematics and not have to code for it.
But in addition, there is a form filled out with 16 different Categories and anywhere from 2 to 13 options to select. They fill out a form with checkboxes and radio buttons. They want to understand how many distinct combinations we have on our programs.
The purpose? I have no idea...I was just tasked 🙂
Again it sounds like you only want to number of possibilities but at other times you said you want a resultset with details about what they are. Looks like Matt came along and corrected both of us on calculations. Does that get you what you need?
I need both. For the first request, they want total number of possiblities...for the 2nd request, they want the total different combinations on the programs we have.
I believe that Matt has corrected us, as I don't wan to count Blue/Green and Green/Blue as 2 different combinations, they are essentially the same, and should be counted as 1.
February 6, 2012 at 12:51 pm
Pretty sure you can handle the counts now.
As to getting the resultset of all possibilities...
What do you want that to look like? I know it was just assigned to you but I don't see anything that could be practical from having a list of combinations like this. I also don't begin to know how to pull that, which is probably why I keep asking about the practicality of it. 😎
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 6, 2012 at 1:02 pm
I think there's a small issue with the math. Using factorial would work for permutations (i.e. the orders of the colors matter), but is not right for combinations. So if the order in which the colors are specified is NOT important,
To know all non-repeating combinations of length d withing a distinct population of n you're looking for n!/((n-d)!d!)
To know all non-repeating combinations of all lengths within a distinct population of n you're looking for (2^n)-1
So for four colors it is (2^4) - 1 -= 15, 5 flavors yields (2^5)-1 = 31, so total combinations would be:
2*15*3*31 = 2790
Thanks Sean...appreciated and hopefully I will now remember the difference between combs and perms (maybe 😀 )
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 6, 2012 at 1:06 pm
Sean Lange (2/6/2012)
Pretty sure you can handle the counts now.As to getting the resultset of all possibilities...
What do you want that to look like? I know it was just assigned to you but I don't see anything that could be practical from having a list of combinations like this. I also don't begin to know how to pull that, which is probably why I keep asking about the practicality of it. 😎
I'm not sure what I want it to look like! Its a crazy request and I don't see the point, but I won't win if I argue. 😀 Thanks for all of your help, and if any ideas come to mind, feel free to share, and if anything comes to mine, I will post my thoughts!
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply