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 Tuesday, June 03, 2008 8:09 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 1,292, Visits: 782
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)

Post #511082
Posted Wednesday, June 04, 2008 1:10 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:04 AM
Points: 1,384, Visits: 1,240
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.
Post #511170
Posted Wednesday, June 04, 2008 2:27 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, October 21, 2013 12:21 PM
Points: 1,149, Visits: 871
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. :)
Post #511195
Posted Wednesday, June 04, 2008 6:03 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, March 20, 2014 7:32 AM
Points: 5,181, Visits: 1,368
Refreshing one ..........:)


Post #511285
Posted Wednesday, June 04, 2008 6:41 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, April 11, 2014 1:51 PM
Points: 2,628, Visits: 760
I agree - great question!

Jamie
Post #511308
Posted Wednesday, June 04, 2008 6:53 AM
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: Yesterday @ 7:06 AM
Points: 3,878, Visits: 1,086
That one made me slow down and think for a while.
Post #511317
Posted Wednesday, June 04, 2008 7:05 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 4:10 AM
Points: 1,553, Visits: 2,232
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


Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Post #511327
Posted Wednesday, June 04, 2008 7:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 08, 2011 8:52 AM
Points: 26, Visits: 79
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.
Post #511338
Posted Wednesday, June 04, 2008 8:03 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 9:28 PM
Points: 554, Visits: 1,178
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)
Post #511393
Posted Wednesday, June 04, 2008 8:32 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 1,292, Visits: 782
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)

Post #511424
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse