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

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Todd Carrier » Know your UNION(s), NULL(s), COUNT(s) ?

Add to briefcase

25 posts, Page 2 of 3

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

Expand / Collapse

Author

Message

Posted Wednesday, June 04, 2008 9:22 AM

SSChasing Mays

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 12:04 PM
Points: 612, Visits: 209

I totally missed Last Union but Count(ColumnName) saved my 2 points Hehe

Hehe

. Gr8 to have this kind of questions. As it clears/remind the small differences between similar options.

Thanks...It was fun...:)

Post #511475

Posted Wednesday, June 04, 2008 9:34 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 29, 2010 6:28 AM
Points: 97, Visits: 64

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!

Post #511490

Posted Wednesday, June 04, 2008 12:20 PM

Hall of Fame

Group: General Forum Members
Last Login: Monday, July 30, 2012 10:42 AM
Points: 3,434, Visits: 519

I did not miss the last ALL in UNION, I guessed it was a trick
What a good question!

Regards,
Yelena Varshal

Post #511659

Posted Wednesday, June 04, 2008 12:53 PM

SSC Eights!

Group: General Forum Members
Last Login: Wednesday, April 10, 2013 3:04 PM
Points: 839, Visits: 938

Good Question!

Could somebody please explain the difference between count(*) and count(1)?

Post #511695

Posted Wednesday, June 04, 2008 3:17 PM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 2:54 PM
Points: 1,049, Visits: 633

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

Post #511818

Posted Wednesday, June 04, 2008 3:23 PM

Hall of Fame

Group: General Forum Members
Last Login: Monday, July 30, 2012 10:42 AM
Points: 3,434, Visits: 519

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

Post #511824

Posted Wednesday, June 04, 2008 3:34 PM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 2:54 PM
Points: 1,049, Visits: 633

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

Post #511832

Posted Wednesday, June 04, 2008 4:07 PM

Hall of Fame

Group: General Forum Members
Last Login: Monday, July 30, 2012 10:42 AM
Points: 3,434, Visits: 519

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

Post #511845

Posted Wednesday, June 04, 2008 5:35 PM

SSC Eights!

Group: General Forum Members
Last Login: Wednesday, April 10, 2013 3:04 PM
Points: 839, Visits: 938

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.

Post #511862

Posted Tuesday, June 10, 2008 11:41 AM

Valued Member

Group: General Forum Members
Last Login: Friday, August 17, 2012 6:07 PM
Points: 62, Visits: 124

I suppose that one would use count(1) to increase the mystery in one's uncommented code. Hehe

Hehe

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

Post #514676

« Prev Topic | Next Topic »

Add to briefcase

25 posts, Page 2 of 3

Permissions

Expand / Collapse