Counting values

  • Comments posted to this topic are about the item Counting values

  • easy one.

    But the explanation is wrong - NULL values aren't counted, only rows are counted and a row can't be NULL.

    Tom

  • Sure, Explanation is wrong -

    You can understand this by below query :

    CREATE TABLE mytable ( myid INT, mychar VARCHAR(10) );

    GO

    INSERT mytable

    ( myid, mychar )

    VALUES

    ( 1, 'A' ),

    ( 2 , 'B'),

    ( NULL, 'C' ),

    ( 4, 'D' ),

    ( NULL, 'E' ),(null,null),(null,null)

    , ( 6, 'F' );

    SELECT

    COUNT(myid),count(*),count(mychar)

    FROM

    mytable;

    DROP TABLE mytable;

    Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature

  • This was removed by the editor as SPAM

  • Actually, it returns:

    Msg 102, Level 15, State 1, Line 4

    Incorrect syntax near ','.

  • Actually it depends:-P

    Nice example of your ANSI settings affecting the results:

    If you define your table as:

    CREATE TABLE mytable ( myid INT null, mychar VARCHAR(10) );

    Or

    set ansi_defaults ON

    CREATE TABLE mytable ( myid INT , mychar VARCHAR(10) )

    then it will always work and give 6

    *If* you define your table as

    set ansi_defaults off

    CREATE TABLE mytable ( myid INT , mychar VARCHAR(10) )

    Then you will get the expected error.

  • Yet Another DBA (1/14/2015)


    Actually it depends:-P

    ...

    Then you will get the expected error.

    Generally Saying...

    After recreating the table with ANSI_DEFAULTS OFF, when tried to insert the records, you will see the below error.

    Msg 515, Level 16, State 2, Server AA\BBYY, Line 1

    Cannot insert the value NULL into column 'myid', table 'LocalWork.dbo.mytable'; column does not allow nulls. INSERT fails.

    Its like setting the COLUMN to NOT NULL and trying to insert NULL, which of-course, SQL has to do its job and stop and warn the user who is trying to insert NULL.

    (correct me if I am wrong) But the behaviour of the COUNT(*) will remains the same, it is just used to count all the rows (NULL or NOT NULL does not matter). COUNT(*) works on the data which is already in the table and it just returns the count. It does not worries about the SETings done in that specific connection. Count(*) is the "returning the result" part where as setting the ANSI_DEFAULTS OFF at the time of table creation and trying to insert data after table created is the "creation part" (the other half).

    (and this another age old debate, like usage of collation, if there is no any SET option mentioned then the SSC'ian has to consider the default values πŸ˜‰ )

    //-edit; Added last to lines and fixed some typoes

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Thank you for the post, Steve, good one.

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • This was removed by the editor as SPAM

  • Stewart "Arturius" Campbell (1/14/2015)


    edwardwill (1/14/2015)


    Actually, it returns:

    Msg 102, Level 15, State 1, Line 4

    Incorrect syntax near ','.

    what version of SQL Server are you using?

    2008 R2 SP1

  • Stewart "Arturius" Campbell (1/14/2015)


    COUNT(*) returns total number of rows, irrespective of the contents.

    +1

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • This was removed by the editor as SPAM

  • edwardwill (1/14/2015)


    Actually, it returns:

    Msg 102, Level 15, State 1, Line 4

    Incorrect syntax near ','.

    Perhaps you are missing a comma?

  • briankwartler (1/14/2015)


    edwardwill (1/14/2015)


    Actually, it returns:

    Msg 102, Level 15, State 1, Line 4

    Incorrect syntax near ','.

    Perhaps you are missing a comma?

    No, it's version-specific. The code runs on 2008+.

    β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Nice but somewhat easy question (especially after the quite lengthy discussion of NULLs and aggregates last week). Not sure it is worth 2 points.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

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

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