June 3, 2008 at 8:09 pm
Comments posted to this topic are about the item Know your UNION(s), NULL(s), COUNT(s) ?
Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012)
June 4, 2008 at 1:10 am
Fun question!
http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
June 4, 2008 at 2:27 am
Before answering I was wondering if missing ALL on final union was intentional or a typo.
Count(*) counts all rows, Count([ALL] column) counts rows where column is not null, so it doesn't matter whether final union is just "union" or "union all".
Fun question anyway. 🙂
June 4, 2008 at 6:03 am
Refreshing one ..........:)
June 4, 2008 at 6:41 am
I agree - great question!
Jamie
June 4, 2008 at 6:53 am
That one made me slow down and think for a while.
June 4, 2008 at 7:05 am
I also had to sit down for a bit on this one 😉
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 4, 2008 at 7:17 am
At first I was wondering if it was a typo as well with the missing UNION ALL. However, it still got me. Keep up the fun questions, I look forward to them each day.
June 4, 2008 at 8:03 am
Ya, great question.
I didn't realize the subtle different between Count(*), Count(1) and Count(expression)
Count(*) - Includes Nulls
Count(1) - Includes Nulls
Count(expression) - Does not include nulls ie Count(ColumnName)
June 4, 2008 at 8:32 am
It had been a while since I wrote the question, and I MISSED IT! The subtle but important difference between count (*) and count (columnname) is something I will have to continue to drill into my think skull!
Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012)
June 4, 2008 at 9:22 am
I totally missed Last Union but Count(ColumnName) saved my 2 points:hehe:. Gr8 to have this kind of questions. As it clears/remind the small differences between similar options.
Thanks...It was fun...:)
June 4, 2008 at 9:34 am
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!
June 4, 2008 at 12:20 pm
I did not miss the last ALL in UNION, I guessed it was a trick
What a good question!
Regards,Yelena Varsha
June 4, 2008 at 12:53 pm
Good Question!
Could somebody please explain the difference between count(*) and count(1)?
June 4, 2008 at 3:17 pm
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)
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply