January 21, 2015 at 8:11 pm
Comments posted to this topic are about the item Distinct Counts
January 21, 2015 at 11:45 pm
Thank you Steve, good one. Anyway, I selected NO.
But the trick or catch here is;
1. If those two queries are executed "only" considering the given data inserted into the table, then answer is "Yes" - it does returns same count all the time
2. If, let say, add one more row "INSERT myTable SELECT 1, NULL" and then if those two queries are executed then of-course the result is not the same.
When I answered the question, I was focusing on the #2 point and selected "NO" and submitted; and all of a sudden "what-if this is pertained only to this data?" I was seeing #1... well it was too late;
This is one of those questions with "it depends" on the data.
(This is just my point of view-) Probably the hint on the data like, "only to this" or "in generic" would increase the chance of answering the question more accurately.
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
January 22, 2015 at 12:51 am
Got this one wrong. I was thinking NULL would be counted as a distinct value, and I realized too late I was confusing it with unique indexes.
Ah well, very nice question Steve.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 22, 2015 at 1:22 am
Koen Verbeeck (1/22/2015)
Got this one wrong. I was thinking NULL would be counted as a distinct value, and I realized too late I was confusing it with unique indexes.Ah well, very nice question Steve.
gotcha 🙂
by the way nice question.
January 22, 2015 at 1:34 am
This was removed by the editor as SPAM
January 22, 2015 at 2:27 am
Too bad the explanation claims that NULL values are considered with * is used. Using * makes COUNT use rows, not values, and rows cannot be NULL.
Other then that, the question is good. With the caveat that, withh this table design, the two queries COULD return different results. JUst not with the given sample data.
January 22, 2015 at 2:37 am
The query would fail if run against a case sensitive database due to myid and MyID being seen as two different columns in that case (Although nothing was specified so as always - assume defaults!).
Got it correct by just taking into account the data given - although as stated the results would be different with a changed dataset, but can only go off what we've been given!
January 22, 2015 at 2:53 am
Hugo Kornelis (1/22/2015)
Too bad the explanation claims that NULL values are considered with * is used. Using * makes COUNT use rows, not values, and rows cannot be NULL.Other then that, the question is good. With the caveat that, withh this table design, the two queries COULD return different results. JUst not with the given sample data.
+1
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
January 22, 2015 at 5:06 am
Good question. Thanks
---------------
Mel. 😎
January 22, 2015 at 5:24 am
Good question, Steve. I like how you keep finding new twists on COUNT to ask - very nice. Even the simple COUNT function has nuances that make you stop and think. And COUNT isn't even complicated. It just goes to show that there's no substitute for knowing the functions from having learned the language.
January 22, 2015 at 5:27 am
Wahoo got lucky today! (kidding) - Thanks for the question 😀
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 22, 2015 at 5:56 am
For the first time in weeks I answered with absolute confidence and no hesitation - and was correct! 😀
January 22, 2015 at 6:27 am
I have recently used the count of distinct values. So I was confident to answer.
Thanks for the question steve
January 22, 2015 at 7:24 am
Nice question
January 22, 2015 at 7:42 am
Thanks for the question.
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply