SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Harit Gohel
Harit Gohel
Right there with Babe
Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)

Group: General Forum Members
Points: 718 Visits: 232
I totally missed Last Union but Count(ColumnName) saved my 2 pointsHehe. Gr8 to have this kind of questions. As it clears/remind the small differences between similar options.

Thanks...It was fun...Smile
Andrea Allred
Andrea Allred
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 93
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!
Yelena Varshal
Yelena Varshal
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8734 Visits: 600
I did not miss the last ALL in UNION, I guessed it was a trick
What a good question!


Regards,
Yelena Varshal

Kangana Beri
Kangana Beri
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1094 Visits: 954
Good Question!

Could somebody please explain the difference between count(*) and count(1)?
Todd Carrier
Todd Carrier
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2261 Visits: 954
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)
Yelena Varshal
Yelena Varshal
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8734 Visits: 600
This is in regards to the previous post:
Why we can select count from other positions?
SELECT COUNT (25) FROM #TEST

We can not order by 25:
SELECT * FROM #TEST ORDER BY 25 DESC
this returns a message:
Msg 108, Level 16, State 1, Line 1
The ORDER BY position number 25 is out of range of the number of items in the select list.

But we do can do COUNT(25)


Regards,
Yelena Varshal

Todd Carrier
Todd Carrier
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2261 Visits: 954
Aha,

I am wrong in assuming COUNT (1) refers to the first column.

SELECT COUNT (25)
--or
SELECT COUNT ('something')


these both return 1, as the count of the literal expression, be it integer or character

Similar to
SELECT 1,2,3,'something' FROM #TEMP


Notice that 1,2,3, and the string above are literals.

Can anyone thing of another instance where you would refer to the ordinal positions of columns as 1,2, or 3... other than and ORDER BY?

Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012)
Yelena Varshal
Yelena Varshal
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8734 Visits: 600
Sure! A lot. In VB or other front end in relation to Recordset or Dataset references or controls like GRID control.
How it relates to SQL? We can use this code in CLR Stored Procedures for example.

This is an example for VBA:

http://msdn.microsoft.com/en-us/library/aa192404.aspx

If Activedocument.Tables.Count >= 1 Then
Set myTable = Activedocument.Tables(1)
myTable.Columns.Add BeforeColumn:=myTable.Columns(1)
myTable.Columns.DistributeWidth
End If

OR
http://msdn.microsoft.com/en-us/library/wc06dx4f.aspx

// Set the column header names.
dataGridView.Columns[0].Name = "Recipe";
dataGridView.Columns[1].Name = "Category";
dataGridView.Columns[2].Name = thirdColumnHeader;
dataGridView.Columns[3].Name = "Rating";


Regards,
Yelena Varshal

Kangana Beri
Kangana Beri
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1094 Visits: 954
So, in which situation would you use count(1) instead of a count(*)? Or is it even a good practice to use count(1)??

To me it seems that they are essentially doing the same job when used with a FROM clause but the meaning of count(*) is very clear.
Pat Reiter
Pat Reiter
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 125
I suppose that one would use count(1) to increase the mystery in one's uncommented code.Hehe

Here's a justification for count(1):
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1242198&SiteID=1
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search