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

  • TaylorMade

    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
    © Dr.Plch

  • venoym

    SSCarpal Tunnel

    Points: 4161

    TaylorMade (4/18/2013)


    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.

    I think this help you. 🙂

    +1

    Thanks for the explanation, I couldnt seem to get this clear in my head.

Viewing 15 posts - 16 through 30 (of 35 total)

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