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

 Author Message Harit Gohel SSChasing Mays Group: General Forum Members Points: 640 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... Andrea Allred SSC-Enthusiastic Group: General Forum Members Points: 108 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 SSCarpal Tunnel Group: General Forum Members Points: 4552 Visits: 595 I did not miss the last ALL in UNION, I guessed it was a trickWhat a good question! Regards,Yelena Varshal Kangana Beri SSC Eights! Group: General Forum Members Points: 916 Visits: 954 Good Question!Could somebody please explain the difference between count(*) and count(1)? Todd Carrier SSCommitted Group: General Forum Members Points: 1907 Visits: 936 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) Yelena Varshal SSCarpal Tunnel Group: General Forum Members Points: 4552 Visits: 595 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 Todd Carrier SSCommitted Group: General Forum Members Points: 1907 Visits: 936 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) Yelena Varshal SSCarpal Tunnel Group: General Forum Members Points: 4552 Visits: 595 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 Kangana Beri SSC Eights! Group: General Forum Members Points: 916 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 Valued Member Group: General Forum Members Points: 64 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