Counting values

Question

Post reply

Counting values

Steve Jones - SSC Editor

SSC Guru

Points: 736173
More actions
January 13, 2015 at 8:31 pm

#294371

Comments posted to this topic are about the item Counting values
January 14, 2015 at 12:48 am

This was removed by the editor as SPAM
January 14, 2015 at 2:21 am

This was removed by the editor as SPAM
January 14, 2015 at 4:05 am

This was removed by the editor as SPAM

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

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

TomThomson SSC Guru Points: 104773 More actions · Answer 1

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 More actions · Answer 2

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

edwardwill SSCertifiable Points: 5851 More actions · Answer 3

Actually, it returns:

Msg 102, Level 15, State 1, Line 4

Incorrect syntax near ','.

Yet Another DBA SSCarpal Tunnel Points: 4299 More actions · Answer 4

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 More actions · Answer 5

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 More actions · Answer 6

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.

edwardwill SSCertifiable Points: 5851 More actions · Answer 7

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: 13921 More actions · Answer 8

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

briankwartler Ten Centuries Points: 1332 More actions · Answer 9

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: 186127 More actions · Answer 10

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 11

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