Distinct Counts

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720963

    Comments posted to this topic are about the item Distinct Counts

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    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.

  • Koen Verbeeck

    SSC Guru

    Points: 258975

    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

  • twin.devil

    SSC-Insane

    Points: 22208

    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.

  • This was removed by the editor as SPAM

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Heals

    SSCrazy

    Points: 2234

    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!

  • Hany Helmy

    SSChampion

    Points: 13488

    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

  • SqlMel

    SSCrazy

    Points: 2891

    Good question. Thanks

    ---------------
    Mel. 😎

  • Ed Wagner

    SSC Guru

    Points: 286985

    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.

  • MyDoggieJessie

    SSC-Forever

    Points: 44280

    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

  • Gavin Regnart

    Old Hand

    Points: 303

    For the first time in weeks I answered with absolute confidence and no hesitation - and was correct! 😀

  • pmadhavapeddi22

    SSCertifiable

    Points: 5306

    I have recently used the count of distinct values. So I was confident to answer.

    Thanks for the question steve

  • RK Mandava

    SSCarpal Tunnel

    Points: 4153

    Nice question

  • Dana Medley

    SSCertifiable

    Points: 6764

    Thanks for the question.



    Everything is awesome!

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

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