Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Know your UNION(s), NULL(s), COUNT(s) ? Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, June 4, 2008 9:22 AM
 SSChasing Mays Group: General Forum Members Last Login: Wednesday, August 10, 2016 2:45 PM Points: 612, Visits: 232
 I totally missed Last Union but Count(ColumnName) saved my 2 points. 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 4, 2008 9:34 AM
 SSC Journeyman Group: General Forum Members Last Login: Wednesday, December 2, 2015 10:06 AM Points: 98, 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!
Post #511490
 Posted Wednesday, June 4, 2008 12:20 PM
 Hall of Fame Group: General Forum Members Last Login: Friday, November 18, 2016 8:43 AM Points: 3,476, Visits: 592
 I did not miss the last ALL in UNION, I guessed it was a trickWhat a good question! Regards,Yelena Varshal
Post #511659
 Posted Wednesday, June 4, 2008 12:53 PM
 SSC Eights! Group: General Forum Members Last Login: Friday, October 24, 2014 4:29 PM Points: 872, Visits: 954
 Good Question!Could somebody please explain the difference between count(*) and count(1)?
Post #511695
 Posted Wednesday, June 4, 2008 3:17 PM
 SSCommitted Group: General Forum Members Last Login: Thursday, December 1, 2016 3:20 PM Points: 1,700, Visits: 918
 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 #TESTSELECT 1,'DOG',GETDATE() UNIONSELECT 2,'CAT',GETDATE() UNIONSELECT 3,'BIRD',NULL UNIONSELECT 4, NULL, GETDATE() SELECT * FROM #TESTSELECT * FROM #TEST ORDER BY 1 DESCSELECT * FROM #TEST ORDER BY 2 DESCSELECT * FROM #TEST ORDER BY 3 DESCSELECT COUNT (1) FROM #TESTSELECT COUNT (2) FROM #TESTSELECT COUNT (3) FROM #TESTSELECT COUNT (A) FROM #TESTSELECT COUNT (B) FROM #TESTSELECT COUNT (C) FROM #TESTSELECT COUNT (*) FROM #TEST` Todd CarrierMCITP - Database Administrator (SQL 2008)MCSE: Data Platform (SQL 2012)
Post #511818
 Posted Wednesday, June 4, 2008 3:23 PM
 Hall of Fame Group: General Forum Members Last Login: Friday, November 18, 2016 8:43 AM Points: 3,476, Visits: 592
 This is in regards to the previous post:Why we can select count from other positions?SELECT COUNT (25) FROM #TESTWe can not order by 25:SELECT * FROM #TEST ORDER BY 25 DESCthis returns a message:Msg 108, Level 16, State 1, Line 1The 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 4, 2008 3:34 PM
 SSCommitted Group: General Forum Members Last Login: Thursday, December 1, 2016 3:20 PM Points: 1,700, Visits: 918
 Aha, I am wrong in assuming COUNT (1) refers to the first column.`SELECT COUNT (25)--orSELECT COUNT ('something')`these both return 1, as the count of the literal expression, be it integer or characterSimilar 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 CarrierMCITP - Database Administrator (SQL 2008)MCSE: Data Platform (SQL 2012)
Post #511832
 Posted Wednesday, June 4, 2008 4:07 PM
 Hall of Fame Group: General Forum Members Last Login: Friday, November 18, 2016 8:43 AM Points: 3,476, Visits: 592
 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.aspxIf Activedocument.Tables.Count >= 1 Then Set myTable = Activedocument.Tables(1) myTable.Columns.Add BeforeColumn:=myTable.Columns(1) myTable.Columns.DistributeWidthEnd IfORhttp://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 4, 2008 5:35 PM
 SSC Eights! Group: General Forum Members Last Login: Friday, October 24, 2014 4:29 PM Points: 872, 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.
Post #511862
 Posted Tuesday, June 10, 2008 11:41 AM
 Valued Member Group: General Forum Members Last Login: Saturday, January 25, 2014 2:45 PM Points: 62, Visits: 125
 I suppose that one would use count(1) to increase the mystery in one's uncommented code.Here's a justification for count(1):http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1242198&SiteID=1
Post #514676

 Permissions