Counting values

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720963

    Comments posted to this topic are about the item Counting values

  • TomThomson

    SSC Guru

    Points: 104773

    easy one.

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

    Tom

  • Vimal Lohani

    SSCommitted

    Points: 1650

    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

  • edwardwill

    SSCertifiable

    Points: 5317

    Actually, it returns:

    Msg 102, Level 15, State 1, Line 4

    Incorrect syntax near ','.

  • Yet Another DBA

    SSCarpal Tunnel

    Points: 4299

    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.

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    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.

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    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

  • edwardwill

    SSCertifiable

    Points: 5317

    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

  • Hany Helmy

    SSChampion

    Points: 13488

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


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

    +1

  • This was removed by the editor as SPAM

  • briankwartler

    Ten Centuries

    Points: 1332

    edwardwill (1/14/2015)


    Actually, it returns:

    Msg 102, Level 15, State 1, Line 4

    Incorrect syntax near ','.

    Perhaps you are missing a comma?

  • ChrisM@Work

    SSC Guru

    Points: 186120

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

    [font="Arial"]β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


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

  • Koen Verbeeck

    SSC Guru

    Points: 258975

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

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