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

    Unanswered: Issue with SQL query

    Hi,

    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.

    Regards,
    Gary

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    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
    INNER
      JOIN mom
        ON mom.reviewId = review.reviewId 
    GROUP
        BY review.reviewForum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2012
    Posts
    6
    @r937

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

    Much appreciated.

    Regards,
    Gary

Posting Permissions

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