Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2012

    Unanswered: Issue with SQL query


    I'm a student on placement within a company. As part of my placement I am creating an internal tool and part of the dashboard has to chart data. I am using fusion charts at present, the problem is, I'm very new to mysql, some of the data is from a number of tables, however the issue I have is as follows. The query I am using is:

    HTML Code:
    SELECT review.reviewForum, mom.result
    FROM review, mom
    WHERE review.reviewId = mom.reviewId;
    This works perfectly, the result that is produced is:

    HTML Code:
    reviewForum	result
    A	        Not Approved
    B	        Approved
    B	        Approved
    B              Cancelled
    C	        Approved
    D	        Cancelled
    That's fine, however, I need to count the results to display something like
    HTML Code:
    reviewForum         Approved          Not Approved     Cancelled
    A                                       1
    B                    2                                    1
    C                    1
    D                                                          1
    I essentially need to chart the status by forum, so how many are approved, not approved and cancelled by forum. So I will have a legend for the three statuses, forums listed on x axis.

    Can anyone help, I really really would appreciate it as I am almost a complete noobie as regards mysql..

    I do understand the text I've wrapped in html tags isn't code, however, it kindof kept the layout I needed.


  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    SELECT review.reviewForum
         , COUNT(CASE WHEN mom.result = 'Approved'
                      THEN 'yes' ELSE NULL END) AS Approved
         , COUNT(CASE WHEN mom.result = 'NOT Approved'
                      THEN 'yes' ELSE NULL END) AS Not_Approved
         , COUNT(CASE WHEN mom.result = 'Cancelled'
                      THEN 'yes' ELSE NULL END) AS Cancelled
      FROM review
      JOIN mom
        ON mom.reviewId = review.reviewId 
        BY review.reviewForum | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2012

    Thank you so much, I certainly wouldn't have gotten that, no matter how much I had tried...

    Much appreciated.


Posting Permissions

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