# Sum and Count

• 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

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

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.

• 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/

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

John

Yes, an unfortunate bit of punctuation.

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