Summing

  • SQLRNNR

    SSC Guru

    Points: 281210

    Thanks for the 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

  • Paul White

    SSC Guru

    Points: 150442

    L' Eomot Inversé (11/22/2011)


    Nice question, but only works for non-empty tables.

    The reason it doesn't work for empty tables is an excellent illustration of SQL's idiotic confustion between emptiness and nullity - only a committee of mathematical illiterates could have decided that teh sum of the empty set should not be zero.

    Strictly, non-empty sets rather than tables. More confusing examples, for fun:

    -- 0

    SELECT COUNT_BIG(*) FROM AdventureWorks.Production.Product AS p WHERE p.ProductID < 0

    -- NULL

    SELECT SUM(1) FROM AdventureWorks.Production.Product AS p WHERE p.ProductID < 0

    -- NULL

    SELECT MAX(X.x) FROM (SELECT ROW_NUMBER() OVER (ORDER BY p.ProductID) FROM AdventureWorks.Production.Product AS p WHERE p.ProductID < 0) AS X (x)

    -- No result

    SELECT MAX(X.x) OVER () FROM (SELECT ROW_NUMBER() OVER (ORDER BY p.ProductID) FROM AdventureWorks.Production.Product AS p WHERE p.ProductID < 0) AS X (x)

  • Anipaul

    SSC-Insane

    Points: 24681

    Nice questions. Answer could have been "Almost the same thing as Count(*)" instead of "The same thing as Count(*)".

  • TomThomson

    SSC Guru

    Points: 104772

    SQL Kiwi (11/22/2011)


    L' Eomot Inversé (11/22/2011)


    Nice question, but only works for non-empty tables.

    The reason it doesn't work for empty tables is an excellent illustration of SQL's idiotic confustion between emptiness and nullity - only a committee of mathematical illiterates could have decided that teh sum of the empty set should not be zero.

    Strictly, non-empty sets rather than tables. More confusing examples, for fun:

    Yes, you are right, I should have said sets (or preferably bags) not tables.

    And your four queries are a nice illustration of where things in SQL are wrong, and where they are right.

    Of course I think the first in your collection is good mathematics and second is bad mathematics. The third is pretty straightforward and seems sensible (no error of mathematics there, sensible use of NULL.

    The fourth is more interesting - I had to think about it for a bit - but I feel it is correct in mathematical terms. That's because I guess that that "OVER ()" means "partition into as many partitions as there are (not necessarily distinct) rows with each partition containing all the rows" (actually the exact meaning doesn't matter here - just that it's asking for some partition to be done - but it might matter in other queries, so I guess I should go and check that whether it really does mean that) so we are asking for a rowset containing the maxima of an empty set of rowsets (because there are no rows in the rowset being partitioned) so the result is an empty rowset.

    Tom

  • Revenant

    SSC-Forever

    Points: 42467

    Tom, I love your new tag line.

  • TomThomson

    SSC Guru

    Points: 104772

    Revenant (11/23/2011)


    Tom, I love your new tag line.

    I love it too - but there may be otheres (perhaps of a different gender persuasion) who don't like it, so I'm keeping my head down.

    Tom

  • Andre Guerreiro

    SSCertifiable

    Points: 7319

    Thank you for the additional info, Paul and Tom.

    Best regards,

    Best regards,

    Andre Guerreiro Neto

    Database Analyst
    http://www.softplan.com.br
    MCITPx1/MCTSx2/MCSE/MCSA

  • Britt Cluff

    SSCertifiable

    Points: 5083

    Good easy question, thanks.

    http://brittcluff.blogspot.com/

Viewing 8 posts - 16 through 23 (of 23 total)

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