# Sum and Count

• John Mitchell-245523

SSC Guru

Points: 148761

To generalise, if you have n tables Table1, Table2, ... , Tablen where for each value of k Tablek has k rows populated with values 1,2,...,k:

`The COUNT is n!`

``` n The SUM is (n-1)! ? k k=1 = (n-1)!n(n+1)/2 ```

`= (n+1)!/2`

John

SSC Eights!

Points: 966

This why you certifiable and me grasshopper (insert bowing emoticon if there was one available)

• honza.mf

SSCertifiable

Points: 5519

John Mitchell-245523 (4/18/2013)

To generalise, if you have n tables Table1, Table2, ... , Tablen where for each value of k Tablek has k rows populated with values 1,2,...,k:

`The COUNT is n!`

``` n The SUM is (n-1)! ? k k=1 = (n-1)!n(n+1)/2 ```

`= (n+1)!/2`

John

Nice one explanation with lovely formulas.

See, understand, learn, try, use efficient

• venoym

SSCarpal Tunnel

Points: 4161

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

Very good explanation Hugo!

I like to think of it in sets of the table and use the properties of multiplication to simplify the problem.

So, for the qotd you have 1 set of Table 2 = 2 rows. (T1 = 1 row, T2 = 2 rows, Cross = 2 rows).

Add Table 3 you get 2 sets of 3 rows (T1xT2 = 2 rows, T3 = 3 rows, Cross = 6 rows).

Count of Table 3 = 6 (above)

Sum Table 3 ID you get 2 sets of Table 3 so: (1+2+3)*2 = (6)*2 = 12

Add Table 4 from the explanation and you get

Table 4 is 6 sets of 4 rows (T1xT2xT3 = 6 rows, T4 = 4 rows, Cross = 24 Rows).

Count of Table 4 ID = 24 (above)

Sum is 6 sets of table 4, so: (1+2+3+4)*6 = (10)*6 = 60

You can simplify it to this semi-abstract:

X = SUM(Value)*COUNT(Cross)

where Value is the item of the table to sum, and COUNT(Cross) is the number of rows in the first N-1 tables.

• db4breakfast

Ten Centuries

Points: 1097

Very good explanation Hugo!

I like to think of it in sets of the table and use the properties of multiplication to simplify the problem.

So, for the qotd you have 1 set of Table 2 = 2 rows. (T1 = 1 row, T2 = 2 rows, Cross = 2 rows).

Add Table 3 you get 2 sets of 3 rows (T1xT2 = 2 rows, T3 = 3 rows, Cross = 6 rows).

Count of Table 3 = 6 (above)

Sum Table 3 ID you get 2 sets of Table 3 so: (1+2+3)*2 = (6)*2 = 12

Add Table 4 from the explanation and you get

Table 4 is 6 sets of 4 rows (T1xT2xT3 = 6 rows, T4 = 4 rows, Cross = 24 Rows).

Count of Table 4 ID = 24 (above)

Sum is 6 sets of table 4, so: (1+2+3+4)*6 = (10)*6 = 60

You can simplify it to this semi-abstract:

X = SUM(Value)*COUNT(Cross)

where Value is the item of the table to sum, and COUNT(Cross) is the number of rows in the first N-1 tables.

nice explanation.

--------------------------------------------------------------------------------------
Hai Ton
My Db4Breakfast blog.

• Revenant

SSC-Forever

Points: 42467

Many thanks for this interesting question!

• Rob Schripsema

SSCertifiable

Points: 7469

Great question. Thanks!

Rob Schripsema
Propack, Inc.

• Primo Dang

SSCrazy

Points: 2643

John Mitchell-245523 (4/18/2013)

To generalise, if you have n tables Table1, Table2, ... , Tablen where for each value of k Tablek has k rows populated with values 1,2,...,k:

`The COUNT is n!`

``` n The SUM is (n-1)! ? k k=1 = (n-1)!n(n+1)/2 ```

`= (n+1)!/2`

John

Fancy formula design, congratulations! :w00t:

Also, thanks for the good question, Gary!

• SQLRNNR

SSC Guru

Points: 281243

Nice question

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

• Miles Neale

SSChampion

Points: 13147

I understood the question and after looking at it closely I got it right. I also understand the explanation as to what happens arithmetically. But what is this technique useful for? OR is is something to be aware of so it can be avoided?

TIA...

Not all gray hairs are Dinosaurs!

• satwikchoudary

Old Hand

Points: 328

Hi,

Can any one explain why does the select command turning to cross join please ??

• John Mitchell-245523

SSC Guru

Points: 148761

satwikchoudary (4/19/2013)

Can any one explain why does the select command turning to cross join please ??

In the old style syntax, a cross join was denoted by separating the tables to be cross joined with commas, without a WHERE clause.

John

• David Conn

SSCertifiable

Points: 5769

Good Question with a Great Debate to follow.

Thanks.

• satwikchoudary

Old Hand

Points: 328

Thank you.. got it

• stormcricket

SSC Enthusiast

Points: 180

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.