April 18, 2013 at 7:34 am
To generalise, if you have n tables Table1, Table2, ... , Tablen where for each value of k Tablek has k rows populated with values 1,2,...,k:
The COUNT is n!
n
The SUM is (n-1)! ? k
k=1
= (n-1)!n(n+1)/2
= (n+1)!/2
John
April 18, 2013 at 7:37 am
This why you certifiable and me grasshopper (insert bowing emoticon if there was one available)
April 18, 2013 at 7:40 am
John Mitchell-245523 (4/18/2013)
To generalise, if you have n tables Table1, Table2, ... , Tablen where for each value of k Tablek has k rows populated with values 1,2,...,k:
The COUNT is n!n
The SUM is (n-1)! ? k
k=1
= (n-1)!n(n+1)/2
= (n+1)!/2
John
Nice one explanation with lovely formulas.
April 18, 2013 at 7:46 am
TaylorMade (4/18/2013)
I don't dispute the answer, but I don't completely agree, or understand the explanation;"The total rows count will be 1*2*3=6! The summary of id3 will be 2*1+2*2+2*3=12! "
Yes, the row count formula is correct, multiply the number of rows from each table, will work for any number of tables that are joined.
The problem I have is the supposed formula to arrive at the SUM. The true formula is 1+2+3+1+2+3 = 12. I don't see how you can apply any multiplication into this formula, at least to a formula that could be used for any set of data/tables, simply doesn't work for SUM.
For example, extrapolate the exercise to include a 4th table, with four values (1,2,3,4), same final select using id4. The results are 60 and 24. The COUNT of 24 works perfectly using and continuing on with the formula 6*4, but how would you continue on using the formula provided for determining the SUM? You can't! SUM by definition "adds" the values together. Only real formula for that one would be: 1+2+3+4+1+2+3+4+1+2+3+4+1+2+3+4+1+2+3+4+1+2+3+4=60
Very good explanation Hugo!
I like to think of it in sets of the table and use the properties of multiplication to simplify the problem.
So, for the qotd you have 1 set of Table 2 = 2 rows. (T1 = 1 row, T2 = 2 rows, Cross = 2 rows).
Add Table 3 you get 2 sets of 3 rows (T1xT2 = 2 rows, T3 = 3 rows, Cross = 6 rows).
Count of Table 3 = 6 (above)
Sum Table 3 ID you get 2 sets of Table 3 so: (1+2+3)*2 = (6)*2 = 12
Add Table 4 from the explanation and you get
Table 4 is 6 sets of 4 rows (T1xT2xT3 = 6 rows, T4 = 4 rows, Cross = 24 Rows).
Count of Table 4 ID = 24 (above)
Sum is 6 sets of table 4, so: (1+2+3+4)*6 = (10)*6 = 60
You can simplify it to this semi-abstract:
X = SUM(Value)*COUNT(Cross)
where Value is the item of the table to sum, and COUNT(Cross) is the number of rows in the first N-1 tables.
April 18, 2013 at 8:23 am
Very good explanation Hugo!
I like to think of it in sets of the table and use the properties of multiplication to simplify the problem.
So, for the qotd you have 1 set of Table 2 = 2 rows. (T1 = 1 row, T2 = 2 rows, Cross = 2 rows).
Add Table 3 you get 2 sets of 3 rows (T1xT2 = 2 rows, T3 = 3 rows, Cross = 6 rows).
Count of Table 3 = 6 (above)
Sum Table 3 ID you get 2 sets of Table 3 so: (1+2+3)*2 = (6)*2 = 12
Add Table 4 from the explanation and you get
Table 4 is 6 sets of 4 rows (T1xT2xT3 = 6 rows, T4 = 4 rows, Cross = 24 Rows).
Count of Table 4 ID = 24 (above)
Sum is 6 sets of table 4, so: (1+2+3+4)*6 = (10)*6 = 60
You can simplify it to this semi-abstract:
X = SUM(Value)*COUNT(Cross)
where Value is the item of the table to sum, and COUNT(Cross) is the number of rows in the first N-1 tables.
nice explanation.
--------------------------------------------------------------------------------------
Hai Ton
My Db4Breakfast blog.
April 18, 2013 at 9:58 am
Many thanks for this interesting question!
April 18, 2013 at 10:05 am
Great question. Thanks!
Rob Schripsema
Propack, Inc.
April 18, 2013 at 10:35 am
John Mitchell-245523 (4/18/2013)
To generalise, if you have n tables Table1, Table2, ... , Tablen where for each value of k Tablek has k rows populated with values 1,2,...,k:
The COUNT is n!n
The SUM is (n-1)! ? k
k=1
= (n-1)!n(n+1)/2
= (n+1)!/2
John
Fancy formula design, congratulations! :w00t:
Also, thanks for the good question, Gary!
April 18, 2013 at 11:09 am
Nice question
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 18, 2013 at 2:45 pm
I understood the question and after looking at it closely I got it right. I also understand the explanation as to what happens arithmetically. But what is this technique useful for? OR is is something to be aware of so it can be avoided?
TIA...
Not all gray hairs are Dinosaurs!
April 19, 2013 at 6:39 am
Hi,
Can any one explain why does the select command turning to cross join please ??
April 19, 2013 at 8:20 am
satwikchoudary (4/19/2013)
Can any one explain why does the select command turning to cross join please ??
In the old style syntax, a cross join was denoted by separating the tables to be cross joined with commas, without a WHERE clause.
John
April 20, 2013 at 3:50 pm
Good Question with a Great Debate to follow.
Thanks.
April 21, 2013 at 12:52 am
Thank you.. got it
April 22, 2013 at 12:23 am
Danny Ocean (4/17/2013)
kapil_kk (4/17/2013)
Nice question... learn something new today.. 🙂But can anyone plz explain me about the SUM part which is returning 12, I am not clear with this section :w00t:
Kapil, Cross join means Cartesian product. When query have multiple joins, it solve it from left to right. So first cross join take place between table #T1 and #T2. It gives 2 (1 *2) rows. Now these 2 rows have cross join with #T3. It gives 6 (2*3) rows. Output of this query look like below.
id1id2id3
111
112
113
121
122
123
Now calculate sum of column id3. its 1+2+3+1+2+3 = 12.
So Total row count is 6 and total of id3 column is 12.
I think this help you. 🙂
+1
Thanks for the explanation, I couldnt seem to get this clear in my head.
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply