Sum and Count

  • Koen Verbeeck

    SSC Guru

    Points: 258940

    Nice question, interesting discussion.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • manik_anu

    SSCrazy

    Points: 2367

    WWDMark (4/18/2013)


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

    nice explanation... thanks for the question....

    but where is the group by.....???? it is working without group by.....?????

    Manik
    You cannot get to the top by sitting on your bottom.

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    manik123 (4/23/2013)


    but where is the group by.....???? it is working without group by.....?????

    If the SELECT clause of a query contains ONLY aggregate functions, you can leave out the GROUP BY. The result will be a single row with the "grand total".

    (I think everybody uses this in the basic form SELECT COUNT(*) FROM Table to get a count of rows from a table; the example here is a bit more complicated, but the same basic idea).


    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/

  • sqlnaive

    SSCoach

    Points: 17435

    Another good question showing basics of joins. Thanks for this simple question with brilliant approach. +10

  • marlon.seton

    SSCrazy

    Points: 2563

    John Mitchell-245523 (4/18/2013)


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

    John

    Yes, an unfortunate bit of punctuation.

Viewing 5 posts - 31 through 35 (of 35 total)

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