Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2012
    Posts
    6

    Unanswered: Count Case issue

    Hi Folks,

    I have a table called mom, in this table I have a column called reasonsForCancellation. There are a number of pre defined reasons for the user to choose. What I am looking to do is count each of the reasons and group them under the reason, for example:

    Insufficient Participants
    5

    This data will ultimately be charted in a pie chart. I was trying a count case but it's not working so for example

    Code:
    SELECT reasonForCancellation  FROM mom, 
    COUNT(CASE WHEN reasonForCancellation = 'Insufficient Participants' THEN 'yes' ELSE NULL END) 
    AS Insufficient Participants,
    I'm not really sure what to do...if anyone can help me...

    the reasons are

    Insufficient participants
    Requirements Issue
    Missed Impacts Issue
    Other

    I really would appreciate anyone's help on this as I'm not very experienced in MySQL

    Regards,
    Gary

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT reasonForCancellation  
         , COUNT(*) as reason_count
      FROM mom
    GROUP
        BY reasonForCancellation
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2012
    Posts
    6
    @r937 Thanks a million!!

    Can I ask quickly, if the reason for cancellation is empty as in that meeting hasn't been cancelled, if Null can I omit it from the count?

    Regards,
    Gary

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT reasonForCancellation  
         , COUNT(*) as reason_count
      FROM mom
     WHERE reasonForCancellation IS NOT NULL 
    GROUP
        BY reasonForCancellation
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2012
    Posts
    6
    Thank You!!!!!!

  6. #6
    Join Date
    Jun 2012
    Posts
    6
    That is great thank you for you help, unfortunately it still displays the null fields. I've attached the capture of the result for your ref. So there is a total of 12 meetings, 2 were cancelled and ten went ahead, so there was no reason for cancellation. I am looking to only extract the data of those with data in the reasonForCancellatoion field.

    I apologise for the silly questions, it's just that I'm learning as I go.

    Kind regards,
    Gary
    Attached Thumbnails Attached Thumbnails Capture.JPG  

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    perhaps the reasonForCancellation values aren't NULL after all, but rather empty strings...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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