Sum and Count

  • Nice question, interesting discussion.

    Need an answer? No, you need a question
    My blog at
    MCSE Business Intelligence - Microsoft Data Platform MVP

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

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

  • 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:
    SQL Server Execution Plan Reference:

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

  • John Mitchell-245523 (4/18/2013)

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


    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