# I want back my Points

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

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

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

• 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โ

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

• Thanks Carlo, I appreciate the brief but concise remarks to links in the explanation. ๐

• 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

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

Change is inevitable... Change for the better is not.

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

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

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

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

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

• 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

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

• Really, really simple. Thanks, Carlo!

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