# Sum and Count

• Comments posted to this topic are about the item Sum and Count

--------------------------------------
;-)βEverything has beauty, but not everyone sees it.β β Confucius

• Nice question - learned something from it ... Again thanks

If everything seems to be going well, you have obviously overlooked something.

Ron

• 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:

_______________________________________________________________
http://www.sqlservercentral.com/articles/Best+Practices/61537/

• 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.

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!

• 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;`

Kingston Dhasian

http://www.sqlservercentral.com/articles/Best+Practices/61537/

• 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.

Thanks Vinay...:-)

Its clear to me now π

_______________________________________________________________
http://www.sqlservercentral.com/articles/Best+Practices/61537/

• 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:

• Are the exclamation marks in the explanation an expression of surprise, or are they supposed to represent factorials?

John

• 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.

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.

See, understand, learn, try, use efficient

• 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.

Thanks for the more in depth explanation, I was struggling to understand how we got 12 also!! Always learning! π

MCITP

Try not! Do or do not, there is no try

email: info@weekendwebdesign.co.uk
Personal Website: http://markallen.co.uk/

• I think it's more about CROSS JOIN than SUM & COUNT.. Nice One..

--
Dineshbabu
Desire to learn new things..

• Dineshbabu (4/18/2013)

I think it's more about CROSS JOIN than SUM & COUNT.. Nice One..

Right Dinesh π

_______________________________________________________________
http://www.sqlservercentral.com/articles/Best+Practices/61537/

• Thanks, that was a good question!

• 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

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.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Viewing 15 posts - 1 through 15 (of 34 total)