Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2012
    Posts
    188

    Unanswered: Coalesce in Aggregate Function

    If you use Coalesce with Count will it return all values even when null? Reason I pose this question to you is that I am running the below query and am getting escalated results than what should be returned. 1st stop on the debug train is how does Coalesce handle it....for example Boston should return only 143 but the query retunrs 194 for Boston?
    Code:
    Select
    Count(NumOnsite),
    originatingCity,
    Coalesce(Convert(varchar(4000),NewspaperNames), Convert(varchar(4000),MagazineNames)) As PaperNames
    From readytoshipOffsite
    Group By originatingCity, Coalesce(Convert(varchar(4000),NewspaperNames), Convert(varchar(4000),MagazineNames))

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Count() returns the number of non-null values in the group. Coalesce() returns the first non-null value in the argument list. When you use Count(Coalesce(<list>)), it will return the number of lists that had any non-null value in them.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2012
    Posts
    188
    Quote Originally Posted by Pat Phelan View Post
    Count() returns the number of non-null values in the group. Coalesce() returns the first non-null value in the argument list. When you use Count(Coalesce(<list>)), it will return the number of lists that had any non-null value in them.

    -PatP
    That would be the inaccuracy then as sometimes NewspaperName & MagazineName can have a value in them. Is there a way to alter the query to only give me the count ONE time, but only show NewspaperName if it is not null, and if it is null show MagazineName but if they are both null show Null? For example, NewspaperName can be null, and magazineName can be null. Or NewspaperName can hold a value, and MagazineName be null, or the flip of that, NewspaperName can be null and magazineName hold a value. NEVER will both fields hold a value other than NULL
    Last edited by jo15765; 02-06-14 at 17:49.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Do a quick query to show:
    Code:
    SELECT Coalesce(NewsPaperName, MagazineName), NewsPaperName, MagazineName
    and the result ought to be more obvious. It is always easier when you have a concrete example in front of you!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Feb 2012
    Posts
    188
    Quote Originally Posted by Pat Phelan View Post
    Do a quick query to show:
    Code:
    SELECT Coalesce(NewsPaperName, MagazineName), NewsPaperName, MagazineName
    and the result ought to be more obvious. It is always easier when you have a concrete example in front of you!

    -PatP
    Check Out This SQLFiddle I used to attempt to recreate my issue:

    http://sqlfiddle.com/#!3/19f7d/2

    If that doesn't work here is the syntax i used:
    Code:
    Create Table dbo.Test
    ( 
      numonSite int,
      newspaperNames varchar(4000),
      magazineNames varchar(4000),
      originatingCity varchar(100)
     )
    
    Insert Into dbo.Test (numonSite, newspaperNames, magazineNames, originatingCity)
    Values('23','News1', ' ', 'Belgium')
    Insert Into dbo.Test (numonSite, newspaperNames, magazineNames, originatingCity)
    Values('3','News1', ' ', 'Belgium')
    Insert Into dbo.Test (numonSite, newspaperNames, magazineNames, originatingCity)
    Values('2','News1', ' ', 'Belgium')
    Insert Into dbo.Test (numonSite, newspaperNames, magazineNames, originatingCity)
    Values('25','News1', ' ', 'Belgium')
    Insert Into dbo.Test (numonSite, newspaperNames, magazineNames, originatingCity)
    Values('32','News1', ' ', 'Belgium')
    Insert Into dbo.Test (numonSite, newspaperNames, magazineNames, originatingCity)
    Values('3',' ', 'News2', 'Madrid')
    Insert Into dbo.Test (numonSite, newspaperNames, magazineNames, originatingCity)
    Values('5',' ', 'News2', 'Madrid')
    Insert Into dbo.Test (numonSite, newspaperNames, magazineNames, originatingCity)
    Values('11',' ', 'News2', 'Madrid')
    Insert Into dbo.Test (numonSite, newspaperNames, magazineNames, originatingCity)
    Values('25',' ', 'News2', 'Madrid')
    Insert Into dbo.Test (numonSite, newspaperNames, magazineNames, originatingCity)
    Values('37',' ', 'News2', 'Madrid')
    
    
    Select
    Count(NumOnsite) As NumberOnSite,
    originatingCity,
    COALESCE(NewspaperNames, MagazineNames) As PaperNames
    From dbo.Test
    Group By originatingCity, Coalesce(NewspaperNames, MagazineNames)
    Which returns the below set of results:
    Code:
    NUMBERONSITE	ORIGINATINGCITY	PAPERNAMES
    5	Madrid	
    5	Belgium	News1
    If I did something incorrectly or am not using the functions properly please someone let me know

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Code:
    Select
    Count(NumOnsite) As NumberOnSite,
    originatingCity,
    COALESCE(NewspaperNames, MagazineNames) As PaperNames
    From dbo.Test
    Group By originatingCity, Coalesce(NewspaperNames, MagazineNames)
    Which returns the below set of results:

    Code:
    NUMBERONSITE	ORIGINATINGCITY	PAPERNAMES
    5	Madrid	
    5	Belgium	News1
    You are counting numonSite.
    And, all values of numonSite(in your example) were not null.
    Then, both of "Count(NumOnsite) As NumberOnSite" showed 5.

    All NewspaperNames of Madrid were one blank string(and not null).
    Then, "COALESCE(NewspaperNames, MagazineNames) As PaperNames" of Madrid showed a blank.
    Last edited by tonkuma; 02-07-14 at 01:10.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please try by inserting some values with null.

    For example:
    Code:
    Insert Into dbo.Test (numonSite, newspaperNames, magazineNames, originatingCity)
    Values(NULL,'News1', ' ', 'Belgium')
    Insert Into dbo.Test (numonSite, newspaperNames, magazineNames, originatingCity)
    Values('3','News1', ' ', 'Belgium')
    Insert Into dbo.Test (numonSite, newspaperNames, magazineNames, originatingCity)
    Values('2','News1', ' ', 'Belgium')
    Insert Into dbo.Test (numonSite, newspaperNames, magazineNames, originatingCity)
    Values('25','News1', ' ', 'Belgium')
    Insert Into dbo.Test (numonSite, newspaperNames, magazineNames, originatingCity)
    Values('32','News1', ' ', 'Belgium')
    Insert Into dbo.Test (numonSite, newspaperNames, magazineNames, originatingCity)
    Values('3',NULL, 'News2', 'Madrid')
    Insert Into dbo.Test (numonSite, newspaperNames, magazineNames, originatingCity)
    Values('5',NULL, 'News2', 'Madrid')
    Insert Into dbo.Test (numonSite, newspaperNames, magazineNames, originatingCity)
    Values('11',' ', 'News2', 'Madrid')
    Insert Into dbo.Test (numonSite, newspaperNames, magazineNames, originatingCity)
    Values('25',' ', 'News2', 'Madrid')
    Insert Into dbo.Test (numonSite, newspaperNames, magazineNames, originatingCity)
    Values('37',' ', 'News2', 'Madrid')
    you will get the results:
    Code:
    NUMBERONSITE ORIGINATINGCITY PAPERNAMES 
    3            Madrid          
    4            Belgium         News1 
    2            Madrid          News2
    Last edited by tonkuma; 02-07-14 at 01:23. Reason: Add another NULL(number of NULLs is 3, now).

  8. #8
    Join Date
    Feb 2012
    Posts
    188
    Quote Originally Posted by tonkuma View Post
    Please try by inserting some values with null.

    For example:
    Code:
    Insert Into dbo.Test (numonSite, newspaperNames, magazineNames, originatingCity)
    Values(NULL,'News1', ' ', 'Belgium')
    Insert Into dbo.Test (numonSite, newspaperNames, magazineNames, originatingCity)
    Values('3','News1', ' ', 'Belgium')
    Insert Into dbo.Test (numonSite, newspaperNames, magazineNames, originatingCity)
    Values('2','News1', ' ', 'Belgium')
    Insert Into dbo.Test (numonSite, newspaperNames, magazineNames, originatingCity)
    Values('25','News1', ' ', 'Belgium')
    Insert Into dbo.Test (numonSite, newspaperNames, magazineNames, originatingCity)
    Values('32','News1', ' ', 'Belgium')
    Insert Into dbo.Test (numonSite, newspaperNames, magazineNames, originatingCity)
    Values('3',NULL, 'News2', 'Madrid')
    Insert Into dbo.Test (numonSite, newspaperNames, magazineNames, originatingCity)
    Values('5',NULL, 'News2', 'Madrid')
    Insert Into dbo.Test (numonSite, newspaperNames, magazineNames, originatingCity)
    Values('11',' ', 'News2', 'Madrid')
    Insert Into dbo.Test (numonSite, newspaperNames, magazineNames, originatingCity)
    Values('25',' ', 'News2', 'Madrid')
    Insert Into dbo.Test (numonSite, newspaperNames, magazineNames, originatingCity)
    Values('37',' ', 'News2', 'Madrid')
    you will get the results:
    Code:
    NUMBERONSITE ORIGINATINGCITY PAPERNAMES 
    3            Madrid          
    4            Belgium         News1 
    2            Madrid          News2
    Ah-ha so it looks like my issue is not with Coalesce, but actually with handling data that ISNULL or an empty string.

    What idea's do you guys have to combat that so my query will function properly w/o me having to update ' ' to NULL?

  9. #9
    Join Date
    Apr 2012
    Posts
    213
    Try something like this:

    Code:
    Select
        Count(NumOnsite) As NumberOnSite,
        originatingCity,
        case when newspaperNames = '' 
            then magazineNames 
            else NewspaperNames 
        end As PaperNames
    From dbo.Test
    Group By 
        originatingCity, 
        case when newspaperNames = '' 
            then magazineNames 
            else NewspaperNames 
        end
    Hope this helps.

  10. #10
    Join Date
    Feb 2012
    Posts
    188
    Quote Originally Posted by imex View Post
    Try something like this:

    Code:
    Select
        Count(NumOnsite) As NumberOnSite,
        originatingCity,
        case when newspaperNames = '' 
            then magazineNames 
            else NewspaperNames 
        end As PaperNames
    From dbo.Test
    Group By 
        originatingCity, 
        case when newspaperNames = '' 
            then magazineNames 
            else NewspaperNames 
        end
    Hope this helps.
    Sweet -- that got it up and running. Thanks to all!

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another expression which is shorter than the CASE expression.

    Note: It might be better to add ORDER BY clause.
    Code:
    SELECT COUNT(NumOnsite) As NumberOnSite
         , OriginatingCity
         , COALESCE(NULLIF(NewspaperNames , ' ') , MagazineNames) As PaperNames
     FROM  dbo.Test
     GROUP BY
           OriginatingCity
         , COALESCE(NULLIF(NewspaperNames , ' ') , MagazineNames)
     ORDER BY
           OriginatingCity
         , PaperNames
    SQL Fiddle

  12. #12
    Join Date
    Feb 2012
    Posts
    188
    Quote Originally Posted by tonkuma View Post
    Another expression which is shorter than the CASE expression.

    Note: It might be better to add ORDER BY clause.
    Code:
    SELECT COUNT(NumOnsite) As NumberOnSite
         , OriginatingCity
         , COALESCE(NULLIF(NewspaperNames , ' ') , MagazineNames) As PaperNames
     FROM  dbo.Test
     GROUP BY
           OriginatingCity
         , COALESCE(NULLIF(NewspaperNames , ' ') , MagazineNames)
     ORDER BY
           OriginatingCity
         , PaperNames
    SQL Fiddle
    Amazing! Thank you!!!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •