April 17, 2013 at 8:29 pm
Comments posted to this topic are about the item Sum and Count
--------------------------------------
;-)“Everything has beauty, but not everyone sees it.” ― Confucius
April 17, 2013 at 8:30 pm
April 17, 2013 at 11:20 pm
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:
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 17, 2013 at 11:31 pm
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. 🙂
Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
April 17, 2013 at 11:32 pm
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:
Change the SELECT query like below and check the values of id3, you will understand why the result.
SELECT *
FROM #T1, #T2,#T3;
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 17, 2013 at 11:39 pm
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. 🙂
Thanks Vinay...:-)
Its clear to me now 🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 18, 2013 at 2:28 am
bitbucket-25253 (4/17/2013)
Nice question ... Again thanks
+1
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
April 18, 2013 at 4:52 am
Are the exclamation marks in the explanation an expression of surprise, or are they supposed to represent factorials?
John
April 18, 2013 at 4:53 am
Danny Ocean (4/17/2013)
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. 🙂
Just for counting the rows or sums in cartesian cross join it does not matter if you solve multiple joins from left or from right.
April 18, 2013 at 4:59 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. 🙂
Thanks for the more in depth explanation, I was struggling to understand how we got 12 also!! Always learning! 😀
email: info@weekendwebdesign.co.uk
Personal Website: http://markallen.co.uk/
Business Website: https://www.weekendwebdesign.co.uk
April 18, 2013 at 5:26 am
I think it's more about CROSS JOIN than SUM & COUNT.. Nice One..
--
Dineshbabu
Desire to learn new things..
April 18, 2013 at 6:42 am
Dineshbabu (4/18/2013)
I think it's more about CROSS JOIN than SUM & COUNT.. Nice One..
Right Dinesh 😛
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 18, 2013 at 6:49 am
Thanks, that was a good question!
April 18, 2013 at 7:04 am
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
April 18, 2013 at 7:14 am
TaylorMade (4/18/2013)
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.
The cross join of the first two tables has two rows, since these two tables have one and two rows respectively. The cross join with the third table will hence cause each row from the third table to be included two times. Since these rows from the third table have values 1, 2, and 3, each of these three values will be present twice in the final result: two 1's (2*1), two 2'2 (2*2), and two 3's (2*3).
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
The cross join of the first three tables results in 6 rows. The cross join with the fourth table will hence cause each row from the fourth table to be included six times. Since these rows from the fourth table have values 1, 2, 3, and 4, each of these four values will be present six times in the final result: six 1's (6*1), six 2'2 (6*2), six 3's (6*3), and six 4's (6*4). 6*1 + 6*2 + 6*3 + 6*4 = 6 + 12 + 18 + 24 = 60.
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply