Know your UNION(s), NULL(s), COUNT(s) ?

  • Todd Carrier

    Hall of Fame

    Points: 3951

    Comments posted to this topic are about the item Know your UNION(s), NULL(s), COUNT(s) ?

    Todd Carrier
    MCITP - Database Administrator (SQL 2008)
    MCSE: Data Platform (SQL 2012)

  • Tao Klerks

    SSCarpal Tunnel

    Points: 4297

    Fun question!

    http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.

  • Robert-378556

    SSCertifiable

    Points: 5542

    Before answering I was wondering if missing ALL on final union was intentional or a typo.

    Count(*) counts all rows, Count([ALL] column) counts rows where column is not null, so it doesn't matter whether final union is just "union" or "union all".

    Fun question anyway. 🙂

  • Anipaul

    SSC-Insane

    Points: 24681

    Refreshing one ..........:)

  • Jamie-2229

    SSCrazy Eights

    Points: 8151

    I agree - great question!

    Jamie

  • LostAccount

    SSCarpal Tunnel

    Points: 4951

    That one made me slow down and think for a while.

  • Christopher Stobbs

    SSC-Insane

    Points: 21098

    I also had to sit down for a bit on this one 😉

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • rbenton-657286

    SSC Rookie

    Points: 42

    At first I was wondering if it was a typo as well with the missing UNION ALL. However, it still got me. Keep up the fun questions, I look forward to them each day.

  • Bradley Deem

    SSCrazy

    Points: 2565

    Ya, great question.

    I didn't realize the subtle different between Count(*), Count(1) and Count(expression)

    Count(*) - Includes Nulls

    Count(1) - Includes Nulls

    Count(expression) - Does not include nulls ie Count(ColumnName)

  • Todd Carrier

    Hall of Fame

    Points: 3951

    It had been a while since I wrote the question, and I MISSED IT! The subtle but important difference between count (*) and count (columnname) is something I will have to continue to drill into my think skull!

    Todd Carrier
    MCITP - Database Administrator (SQL 2008)
    MCSE: Data Platform (SQL 2012)

  • Harit Gohel

    Ten Centuries

    Points: 1254

    I totally missed Last Union but Count(ColumnName) saved my 2 points:hehe:. Gr8 to have this kind of questions. As it clears/remind the small differences between similar options.

    Thanks...It was fun...:)

  • Andrea Allred

    SSC Veteran

    Points: 231

    I am so glad you posted this question. I didn't know the difference in Count. I did get it solely on the Union part. Thanks for teaching me something new today!

  • Yelena Varshal

    SSC-Dedicated

    Points: 34281

    I did not miss the last ALL in UNION, I guessed it was a trick

    What a good question!

    Regards,Yelena Varsha

  • Kangana Beri

    SSCrazy

    Points: 2134

    Good Question!

    Could somebody please explain the difference between count(*) and count(1)?

  • Todd Carrier

    Hall of Fame

    Points: 3951

    KB,

    The 1 in count 1 refers to the column in the first ordinal position, 2 the second, 3 the third, etc.

    Here is a good example below...

    CREATE TABLE #TEST

    (A INT,

    B VARCHAR (5),

    C DATETIME)

    INSERT INTO #TEST

    SELECT 1,'DOG',GETDATE() UNION

    SELECT 2,'CAT',GETDATE() UNION

    SELECT 3,'BIRD',NULL UNION

    SELECT 4, NULL, GETDATE()

    SELECT * FROM #TEST

    SELECT * FROM #TEST ORDER BY 1 DESC

    SELECT * FROM #TEST ORDER BY 2 DESC

    SELECT * FROM #TEST ORDER BY 3 DESC

    SELECT COUNT (1) FROM #TEST

    SELECT COUNT (2) FROM #TEST

    SELECT COUNT (3) FROM #TEST

    SELECT COUNT (A) FROM #TEST

    SELECT COUNT (B) FROM #TEST

    SELECT COUNT (C) FROM #TEST

    SELECT COUNT (*) FROM #TEST

    Todd Carrier
    MCITP - Database Administrator (SQL 2008)
    MCSE: Data Platform (SQL 2012)

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

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