Sum and Count

• chgn01

Hall of Fame

Points: 3531

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

--------------------------------------
;-)“Everything has beauty, but not everyone sees it.” ― Confucius

• Ron McCullough

SSC Guru

Points: 63877

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

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

Ron

Before posting a performance problem please read[/url]

• kapil_kk

SSC-Insane

Points: 21316

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/

• Danny Ocean

SSCertifiable

Points: 6098

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.

id1 id2 id3

1 1 1

1 1 2

1 1 3

1 2 1

1 2 2

1 2 3

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 !!!

• Kingston Dhasian

SSCoach

Points: 19794

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

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

• kapil_kk

SSC-Insane

Points: 21316

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.

id1 id2 id3

1 1 1

1 1 2

1 1 3

1 2 1

1 2 2

1 2 3

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/

• demonfox

SSCertifiable

Points: 6289

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:

• John Mitchell-245523

SSC Guru

Points: 148315

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

John

• honza.mf

SSCertifiable

Points: 5519

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.

id1 id2 id3

1 1 1

1 1 2

1 1 3

1 2 1

1 2 2

1 2 3

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

• WWDMark

Hall of Fame

Points: 3157

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.

id1 id2 id3

1 1 1

1 1 2

1 1 3

1 2 1

1 2 2

1 2 3

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
MCTS - E-Business Card

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

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

• Dineshbabu

Hall of Fame

Points: 3220

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

--
Dineshbabu
Desire to learn new things..

• kapil_kk

SSC-Insane

Points: 21316

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/

• Mike Hays

SSCommitted

Points: 1871

Thanks, that was a good question!

SSC Eights!

Points: 966

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

• Hugo Kornelis

SSC Guru

Points: 64645

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 35 total)

You must be logged in to reply to this topic. Login to reply