## Sum and Count

 Author Message chgn01 SSCrazy Group: General Forum Members Points: 2956 Visits: 2141 Comments posted to this topic are about the item Sum and Count --------------------------------------;-)“Everything has beauty, but not everyone sees it.” ― Confucius bitbucket-25253 SSC-Forever Group: General Forum Members Points: 45805 Visits: 25280 Nice question - learned something from it ... Again thanks If everything seems to be going well, you have obviously overlooked something. RonPlease help us, help you -before posting a question please read Before posting a performance problem please read kapil_kk SSChampion Group: General Forum Members Points: 14355 Visits: 2779 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 _______________________________________________________________To get quick answer follow this link:http://www.sqlservercentral.com/articles/Best+Practices/61537/ Danny Ocean SSCarpal Tunnel Group: General Forum Members Points: 4266 Visits: 1549 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 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 id31 1 11 1 21 1 31 2 11 2 21 2 3Now 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. :-) ThanksVinay Kumar-----------------------------------------------------------------Keep Learning - Keep Growing !!!www.GrowWithSql.com Kingston Dhasian SSChampion Group: General Forum Members Points: 14485 Visits: 5348 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 Change the SELECT query like below and check the values of id3, you will understand why the result.`SELECT * FROM #T1, #T2,#T3;` Kingston DhasianHow to post data/code on a forum to get the best help - Jeff Modenhttp://www.sqlservercentral.com/articles/Best+Practices/61537/ kapil_kk SSChampion Group: General Forum Members Points: 14355 Visits: 2779 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 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 id31 1 11 1 21 1 31 2 11 2 21 2 3Now 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/ demonfox SSCarpal Tunnel Group: General Forum Members Points: 4607 Visits: 1192 bitbucket-25253 (4/17/2013)Nice question ... Again thanks+1 ~ demonfox___________________________________________________________________Wondering what I would do next , when I am done with this one John Mitchell-245523 SSC Guru Group: General Forum Members Points: 106465 Visits: 18617 Are the exclamation marks in the explanation an expression of surprise, or are they supposed to represent factorials?John honza.mf SSCarpal Tunnel Group: General Forum Members Points: 4351 Visits: 1323 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 id31 1 11 1 21 1 31 2 11 2 21 2 3Now 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. See, understand, learn, try, use efficient© Dr.Plch WWDMark SSCrazy Group: General Forum Members Points: 2423 Visits: 530 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 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 id31 1 11 1 21 1 31 2 11 2 21 2 3Now 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! :-D MCITPMCTS - E-Business CardTwitter: WWDMarkTry not! Do or do not, there is no tryemail: info@weekendwebdesign.co.ukPersonal Website: www.markallen-online.co.ukBusiness Website: www.weekendwebdesign.co.uk