Sum and Count

  • chgn01

    Hall of Fame

    Points: 3515

    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

    Please help us, help you -before posting a question please read[/url]
    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:

    _______________________________________________________________
    To get quick answer follow this link:
    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.

    I think this help you. πŸ™‚

    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.

    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

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

    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.

    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

    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.

    I think this help you. πŸ™‚

    Thanks for the more in depth explanation, I was struggling to understand how we got 12 also!! Always learning! πŸ˜€


    MCITP
    MCTS - E-Business Card
    Twitter: WWDMark

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

    email: info@weekendwebdesign.co.uk
    Personal Website: http://markallen.co.uk/
    Business Website: https://www.weekendwebdesign.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 πŸ˜›

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Mike Hays

    SSCommitted

    Points: 1871

    Thanks, that was a good question!

  • TaylorMade

    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

    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.


    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