I want back my Points

  • Carlo Romagnano

    SSC-Insane

    Points: 21877

    Comments posted to this topic are about the item I want back my Points

  • Thom A

    SSC Guru

    Points: 98522

    Nice question. Did make me wonder for second, due to the DISTINCT, but then the brain (coffee) kicked it. I'm impressed that someone chose 5,5,5, even with your nice "(Please don't choose this one)" statement πŸ™‚

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • call.copse

    SSCoach

    Points: 17053

    Thom A - Thursday, April 6, 2017 2:23 AM

    Nice question. Did make me wonder for second, due to the DISTINCT, but then the brain (coffee) kicked it. I'm impressed that someone chose 5,5,5, even with your nice "(Please don't choose this one)" statement πŸ™‚

    I shook my head slightly seeing that I must admit. I guess it could have been a double bluff! Even so...

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71621

    Great question, thanks Carlo
    The DISTINCT with the MIN & MAX was a clever red herring .

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    β€œlibera tute vulgaris ex”

  • gvoshol 73146

    Hall of Fame

    Points: 3144

    An interesting question, but it could have been more interesting.
    You could have added more values, so that the number of 3's and 5's was not equal - and the answer is still the same.
    And the answer is the same, with or without DISTINCT.
    And that applies, even if multiple more 5's are added to the values, because the values are all integers and the average comes out as a integer, truncating the decimal part.

  • George Vobr

    SSCrazy Eights

    Points: 9088

    Thanks Carlo, I appreciate the brief but concise remarks to links in the explanation. πŸ™‚

  • webrunner

    SSC-Dedicated

    Points: 30102

    Thanks for the question. I was relieved to find that the answer was not one of those counter-intuitive ones lol.

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Jeff Moden

    SSC Guru

    Points: 995683

    gvoshol 73146 - Thursday, April 6, 2017 5:50 AM

    An interesting question, but it could have been more interesting.
    You could have added more values, so that the number of 3's and 5's was not equal - and the answer is still the same.
    And the answer is the same, with or without DISTINCT.
    And that applies, even if multiple more 5's are added to the values, because the values are all integers and the average comes out as a integer, truncating the decimal part.

    I agree.... having an extra 3 or 5 (on or  the other) in the values given would have really tested someone's knowledge of NULLs, Aggregates, Integer Math, and Distinct.  Still, great question especially since such a seemingly simple question requires a good bit of the knowledge of the basics.  Hopefully, the people that make it a habit of just running the code to get the answer actually go back and figure out the WHY and the HOW.  Overall, it's a great question.  I'm going to add a COUNT to it and use it as the interview question I ask right after the one about how to get the current date and time. πŸ˜‰

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thom A

    SSC Guru

    Points: 98522

    Jeff Moden - Thursday, April 6, 2017 8:04 AM

     right after the one about how to get the current date and time. πŸ˜‰

    Out of interest, how many people fail that one? I feel like I'm going to be... Unpleasantly unsurprised?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • leddybill

    SSC Enthusiast

    Points: 191

    Good question. I missed the effect of DISTINCT in the AVG aggregation, but as previously stated, the answer didn't change for the values provided.   A better challenge would be to use the values:
    7,7,3,3,3,3,null
    In this case, DISTINCT does affect the results.

  • Kaye Cahs

    SSCarpal Tunnel

    Points: 4135

    Since we all did so well yesterday and today, I think we deserve a treat - like an extra point.
    (:

  • gvoshol 73146

    Hall of Fame

    Points: 3144

    leddybill - Thursday, April 6, 2017 8:25 AM

    Good question. I missed the effect of DISTINCT in the AVG aggregation, but as previously stated, the answer didn't change for the values provided.   A better challenge would be to use the values:
    7,7,3,3,3,3,null
    In this case, DISTINCT does affect the results.

    It doesn't make a difference for me running it on SS2012, because it's doing integer arithmetic.  Is there a different result on other versions?

    Change one of the values to 3.0, and then distinct does matter.

  • leddybill

    SSC Enthusiast

    Points: 191

    gvoshol 73146 - Thursday, April 6, 2017 9:10 AM

    leddybill - Thursday, April 6, 2017 8:25 AM

    Good question. I missed the effect of DISTINCT in the AVG aggregation, but as previously stated, the answer didn't change for the values provided.   A better challenge would be to use the values:
    7,7,3,3,3,3,null
    In this case, DISTINCT does affect the results.

    It doesn't make a difference for me running it on SS2012, because it's doing integer arithmetic.  Is there a different result on other versions?

    Change one of the values to 3.0, and then distinct does matter.

    Hi gvoshol73146. Using SS2012, I still see a difference caused by DISTINCT:
    SELECT MAX(DISTINCT [Points])
      ,MIN(DISTINCT [Points])
      ,AVG(DISTINCT [Points])
      ,AVG(         [Points])
    FROM (VALUES (7)
         ,(7)
         ,(3)
         ,(3)
         ,(3)
         ,(3)
         ,(NULL)
       ) AS V([Points])

    This returns 7,3,5,4 and illustrates the how DISTINCT affects the calcs.  Change the first value to 7.0, and you will get the floating point calculation resulting in 7.0,3.0,5.00000,4.3333

  • gvoshol 73146

    Hall of Fame

    Points: 3144

    leddybill - Thursday, April 6, 2017 9:33 AM

    gvoshol 73146 - Thursday, April 6, 2017 9:10 AM

    leddybill - Thursday, April 6, 2017 8:25 AM

    Good question. I missed the effect of DISTINCT in the AVG aggregation, but as previously stated, the answer didn't change for the values provided.   A better challenge would be to use the values:
    7,7,3,3,3,3,null
    In this case, DISTINCT does affect the results.

    It doesn't make a difference for me running it on SS2012, because it's doing integer arithmetic.  Is there a different result on other versions?

    Change one of the values to 3.0, and then distinct does matter.

    Hi gvoshol73146. Using SS2012, I still see a difference caused by DISTINCT:
    SELECT MAX(DISTINCT [Points])
      ,MIN(DISTINCT [Points])
      ,AVG(DISTINCT [Points])
      ,AVG(         [Points])
    FROM (VALUES (7)
         ,(7)
         ,(3)
         ,(3)
         ,(3)
         ,(3)
         ,(NULL)
       ) AS V([Points])

    This returns 7,3,5,4 and illustrates the how DISTINCT affects the calcs.  Change the first value to 7.0, and you will get the floating point calculation resulting in 7.0,3.0,5.00000,4.3333

    You are correct.  It is data dependent.

    Using 3's and 5's, the average comes out to 4, DISTINCT or not.  That's because the average is 4.xxx which comes out to 4 integer.  Put in enough 3's, though, and it would come out as 3.xxx and report as a 3.

    Change to some 7's, and the average is sometimes 5.xxx and sometimes 4.xxx, depending on how many of the numbers we use.  (Use enough 7's, and it would be 6.xxx)

    I didn't "test" enough data cases! - Still, it shows the problems that can crop up if you aren't paying attention to integer arithmetic.

  • Revenant

    SSC-Forever

    Points: 42467

    Really, really simple. Thanks, Carlo!

Viewing 15 posts - 1 through 15 (of 22 total)

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