January 14, 2015 at 7:11 am
Nice and easy. Thanks for sharing
January 14, 2015 at 7:22 am
Good question. I am rather shocked at how many people got this wrong (12% at the time of this posting). As many times as this has been covered it is scary how many people still don't understand this basic concept.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 14, 2015 at 7:24 am
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?
I copied the script from the site "as is".
January 14, 2015 at 7:25 am
Stewart "Arturius" Campbell (1/14/2015)
edwardwill (1/14/2015)
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
That is strange - this is the type of error I would have expected when executed on a SQL2005 version of SQL server.
Are you not maybe connecting to a SQL2005 instance?
SELECT @@VERSION
(No column name)
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64) Jun 11 2012 16:41:53 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
January 14, 2015 at 7:39 am
Stewart "Arturius" Campbell (1/14/2015)
TomThomson (1/13/2015)
But the explanation is wrong - NULL values aren't counted, only rows are counted and a row can't be NULL.
Agreed
COUNT(*) returns total number of rows, irrespective of the contents.
+1
January 14, 2015 at 7:42 am
edwardwill (1/14/2015)
Stewart "Arturius" Campbell (1/14/2015)
edwardwill (1/14/2015)
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
That is strange - this is the type of error I would have expected when executed on a SQL2005 version of SQL server.
Are you not maybe connecting to a SQL2005 instance?
SELECT @@VERSION
(No column name)
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64) Jun 11 2012 16:41:53 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
You must have copied something wrong. It works just fine as posted.
CREATE TABLE mytable ( myid INT, mychar VARCHAR(10) );
GO
INSERT mytable
( myid, mychar )
VALUES
( 1, 'A' ),
( 2 , 'B'),
( NULL, 'C' ),
( 4, 'D' ),
( NULL, 'E' ),
( 6, 'F' );
SELECT
COUNT(*)
FROM
mytable;
DROP TABLE mytable;
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 14, 2015 at 7:56 am
Indeed!!
Incorrect syntax (on sql 2000)
Works fine on 2008 R2
January 14, 2015 at 8:14 am
"COUNT()" returns total number of rows, irrespective of the contents...unless a specific column is specified that contains a NULL value, then those will be omitted
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 14, 2015 at 8:21 am
Koen Verbeeck (1/14/2015)
Nice but somewhat easy question (especially after the quite lengthy discussion of NULLs and aggregates last week). Not sure it is worth 2 points.
+1, but I'll take the points.
Don Simpson
January 14, 2015 at 8:26 am
Nice, easy one. Thanks Steve.
January 14, 2015 at 8:32 am
MyDoggieJessie (1/14/2015)
"COUNT()" returns total number of rows, irrespective of the contents...unless a specific column is specified that contains a NULL value, then those will be omitted
That's not quite right. "COUNT()" returns total number of items in a group. The "group" is a set of elements, unless * is used, in which case it is a set of rows.
Don Simpson
January 14, 2015 at 8:43 am
DonlSimpson (1/14/2015)
MyDoggieJessie (1/14/2015)
"COUNT()" returns total number of rows, irrespective of the contents...unless a specific column is specified that contains a NULL value, then those will be omittedThat's not quite right. "COUNT()" returns total number of items in a group. The "group" is a set of elements, unless * is used, in which case it is a set of rows.
+1
Here are some sample queries I tried in addition to the early one from Vimal.
create table mytable
(
myid int,
mychar varchar(10)
);
GO
insert mytable ( myid, mychar )
values ( 1, 'A' ),
( 2, 'B' ),
( null, 'C' ),
( 4, 'D' ),
( null, 'E' ),
( 6, 'F' );
-- Returns 6
select count(*)
from mytable;
-- Returns 4
select count(all myid)
from mytable;
-- Returns 4
select count(myid)
from mytable;
-- Returns 4
select count(distinct myid)
from mytable;
-- Example from Vimal
-- returns 4, 6, 6 respectively
select count(myid), count(*), count(mychar)
from mytable;
drop table mytable;
In some ways this is an easy question, but it others it is subtle and worthy of study.
Thanks, Steve!
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
January 14, 2015 at 11:19 am
Great QOD Steve. I would have not thought that this would have that many issues.
Seems that COUNT() does some fun things. I have been using COUNT(1) to mean a count of rows to keep clear in my head that a column name is not involved and I don't have to be concerned with the column contents. The asterisk is a short circuit to the same thing but it looks like it involved columns when that is not true. I have even taken to pronounce the word slightly differently. I say it's your ***-To-Risk.
ATBCharles Kincaid
January 14, 2015 at 12:28 pm
Mat Cooke (1/14/2015)
Indeed!!Incorrect syntax (on sql 2000)
Works fine on 2008 R2
It would also fail on SQL Server 2005. It is the construct of the Insert statement. On SQL 2005 and earlier you would need separate insert statements for each row inserted.
January 14, 2015 at 12:44 pm
Charles Kincaid (1/14/2015)
Great QOD Steve. I would have not thought that this would have that many issues.Seems that COUNT() does some fun things. I have been using COUNT(1) to mean a count of rows to keep clear in my head that a column name is not involved and I don't have to be concerned with the column contents. The asterisk is a short circuit to the same thing but it looks like it involved columns when that is not true. I have even taken to pronounce the word slightly differently. I say it's your ***-To-Risk.
When I first encountered somebody using COUNT(1) I thought it ordinal position and not a constant. Of course the same person who wrote that always used ordinal position for sorting so it seemed a natural progression. That was many moons ago but I have never liked using count(1) for that very reason. It is all preference of course but I have to stop and think about that constant when I see it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 16 through 30 (of 43 total)
You must be logged in to reply to this topic. Login to reply