Results 1 to 8 of 8

Thread: counting Nulls

  1. #1
    Join Date
    Jul 2009
    Posts
    8

    Unanswered: counting Nulls

    I have four tables:

    CLUB: ClubId, Club
    PLAYER: PlayerId, Player, ClubId
    ACTIVITY: ActivityId, Activity
    PLAYER_ACTIVITY: PA_Id, PlayerId, ActivityId

    Heres what I have, the ACTIVITY table is where there are a list of activies (Goal, Red Card, etc) and the PLAYER_ACTIVITY table stores the player and their specified activity.

    Now here is what I want to achieve - I want a query which will do the following;

    Count the Number of Activities of a player (which I achieved easily) and, the hard bit

    If a PlayerActivity has a NULL value, display it as '0'. I know this sounds long winded, but I really need help with this.

    Help would be much appreciated

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Lookup COALESCE in Books Online
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    SELECT PLAYER.Player, 
         SUM(CASE WHEN ActivityId = 1 THEN 1 ELSE 0 END) as NmbrOfGoals, 
         SUM(CASE WHEN ActivityId = 2 THEN 1 ELSE 0 END) as NmbrOfRedCards, 
         SUM(CASE WHEN ActivityId = 3 THEN 1 ELSE 0 END) as NmbrOfEtc, 
         ... 
    FROM PLAYER
       LEFT OUTER JOIN PLAYER_ACTIVITY ON
          PLAYER.playerId = PLAYER_ACTIVITY.PlayerId
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Sep 2009
    Location
    Dallas, TX
    Posts
    6
    Wim's is good - just thought I'd add an alternative using COUNT:

    SELECT PLAYER.Player,
    COUNT(CASE WHEN ActivityId = 1 THEN 1 END) as NmbrOfGoals,
    COUNT(CASE WHEN ActivityId = 2 THEN 1 END) as NmbrOfRedCards,
    COUNT(CASE WHEN ActivityId = 3 THEN 1 END) as NmbrOfEtc,
    ...
    FROM PLAYER
    LEFT OUTER JOIN PLAYER_ACTIVITY ON
    PLAYER.playerId = PLAYER_ACTIVITY.PlayerId
    COUNT with CASE ignores the NULLs so you can't include the ELSE 0 bit

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here's my alternative...
    Code:
    SELECT PLAYER.Player, 
         COUNT(CASE WHEN ActivityId = 1 THEN 'curly' ELSE NULL END) as NmbrOfGoals, 
         COUNT(CASE WHEN ActivityId = 2 THEN 'larry' ELSE NULL END) as NmbrOfRedCards, 
         COUNT(CASE WHEN ActivityId = 3 THEN 'moe'   ELSE NULL END) as NmbrOfEtc, 
         ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Peso proved ESLE 0 is better optimised than ELSE NULL here:
    http://www.dbforums.com/microsoft-sq...lp-needed.html

    Stooge optimisation is of course enourmously better performant than any other known method.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Sep 2009
    Location
    Dallas, TX
    Posts
    6
    Quote Originally Posted by pootle flump
    Peso proved ESLE 0 is better optimised than ELSE NULL here:
    http://www.dbforums.com/microsoft-sq...lp-needed.html

    Stooge optimisation is of course enourmously better performant than any other known method.
    Wow - I love that! I've never even considered such an effect - very cooly (thanks to both of you).

    I will say that "Peso proved ESLE 0 is better optimised than ELSE NULL for SUM() functions". For COUNT(), I didn't find a substantial difference using that same script.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Good point - totally forgot it was for SUM() and would not work for COUNT()
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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