Post reply

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

  • June 3, 2008 at 8:09 pm

    #191854

    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)

  • June 4, 2008 at 1:10 am

    #824019

    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.

  • June 4, 2008 at 2:27 am

    #824038

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

  • June 4, 2008 at 6:03 am

    #824104

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

  • June 4, 2008 at 6:41 am

    #824122

    I agree - great question!

    Jamie

  • June 4, 2008 at 6:53 am

    #824129

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

  • June 4, 2008 at 7:05 am

    #824138

    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

  • June 4, 2008 at 7:17 am

    #824148

    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.

  • June 4, 2008 at 8:03 am

    #824193

    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)

  • June 4, 2008 at 8:32 am

    #824217

    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)

  • June 4, 2008 at 9:22 am

    #824259

    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...:)

  • June 4, 2008 at 9:34 am

    #824270

    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!

  • June 4, 2008 at 12:20 pm

    #824418

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

    What a good question!

    Regards,Yelena Varsha

  • June 4, 2008 at 12:53 pm

    #824449

    Good Question!

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

  • June 4, 2008 at 3:17 pm

    #824558

    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 24 total)

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