Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Know your UNION(s), NULL(s), COUNT(s) ? Expand / Collapse
Author
Message
Posted Wednesday, June 4, 2008 9:22 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, June 23, 2014 6:40 PM
Points: 612, Visits: 212
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

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 4:00 PM
Points: 97, Visits: 72
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

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 12:24 PM
Points: 3,475, Visits: 579
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 4, 2008 12:53 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, April 10, 2014 3:50 PM
Points: 872, Visits: 952
Good Question!

Could somebody please explain the difference between count(*) and count(1)?
Post #511695
Posted Wednesday, June 4, 2008 3:17 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:59 AM
Points: 1,348, Visits: 790
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)

Post #511818
Posted Wednesday, June 4, 2008 3:23 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 12:24 PM
Points: 3,475, Visits: 579
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 4, 2008 3:34 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:59 AM
Points: 1,348, Visits: 790
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)

Post #511832
Posted Wednesday, June 4, 2008 4:07 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 12:24 PM
Points: 3,475, Visits: 579
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 4, 2008 5:35 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, April 10, 2014 3:50 PM
Points: 872, Visits: 952
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

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse