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 3, 2008 8:09 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 12:48 PM
Points: 1,363, Visits: 796
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 4, 2008 1:10 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 25, 2014 6:37 AM
Points: 1,385, Visits: 1,242
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 4, 2008 2:27 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:03 AM
Points: 1,199, Visits: 879
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 4, 2008 6:03 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:09 AM
Points: 5,338, Visits: 1,385
Refreshing one ..........:)


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

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, June 28, 2014 8:50 AM
Points: 2,649, Visits: 766
I agree - great question!

Jamie
Post #511308
Posted Wednesday, June 4, 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:43 AM
Points: 3,996, Visits: 1,171
That one made me slow down and think for a while.
Post #511317
Posted Wednesday, June 4, 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 4, 2008 7:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 8, 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 4, 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: Friday, August 8, 2014 2:17 PM
Points: 554, Visits: 1,193
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 4, 2008 8:32 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 12:48 PM
Points: 1,363, Visits: 796
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