Assistance with MySQL statement So I have a database of information that is imported from a separate application. This table contains any number of 'rule' results, each rule result is of a certain type ('rule_1', 'rule_2', etc).  This table could contain multiple rule results with the same type.  Each rule result has a rule result value of P (passed), V (violation) or W (warning).  

I'm trying to get a count of how many rules (not rule results) are of each rule result value (P, V or W) for a given code (1.2) with the following understanding...If any of the 'rule_1' records are a result of V then the count would be increased for V regardless of if 1 rule result value was V or if 100 are.

In simple terms I'm summarizing the V, W, P counts for the code 1.2, with V > W > P in terms of weighting.

+---------+--------+------+
| rule_id | result | code |
+---------+--------+------+
  RULE_1      V      1.2
  RULE_1      W      1.2
  RULE_1      P      1.2
  RULE_2      W      1.2
  RULE_2      P      1.2


Expected result would be

+---------+---------+---------+
| v_count | w_count | p_count |
+---------+---------+---------+
    1          1         0


Currently I'm using the following SQL statement, my question is, is this the way to do it or am I missing something simple, this statement seems very convaluted.

SELECT  DISTINCT
    (
        SELECT  COUNT(DISTINCT rule_id)
            from  ruleresult
            WHERE  code = "1.2"
              AND  result = "V"
    ) as v_count, 
    (
        SELECT  COUNT(DISTINCT rule_id)
            from  ruleresult
            WHERE  code = "1.2"
              AND  result = "W"
              AND  rule_id NOT IN (
                SELECT  rule_id
                    FROM  ruleresult
                    WHERE  code = "1.2"
                      AND  result IN ("V"))
    ) as w_count, 
    (
        SELECT  COUNT(DISTINCT rule_id)
            from  ada_ruleresult
            WHERE  code = "1.2"
              AND  result = "P"
              AND  rule_id NOT IN (
                SELECT  rule_id
                    FROM  ruleresult
                    WHERE  code = "1.2"
                      AND  result IN ("V", "W"))
    ) as p_count
    from  ruleresult;
Your basic query is correct, and I don't really see how you could make it simpler, given the definition of what you want to count.

You can simplify it slightly. You don't need the first DISTINCT; and you actually don't need a main FROM:

SELECT
   (SELECT COUNT(DISTINCT rule_id) 
      FROM ruleresult 
     WHERE code = '1.2' AND result = 'V') as v_count,
   (SELECT COUNT(DISTINCT rule_id) 
      FROM ruleresult 
     WHERE code = '1.2' 
        AND result = 'W' 
        AND rule_id NOT IN 
        (SELECT rule_id 
           FROM ruleresult 
          WHERE code = '1.2' 
            AND result IN ('V'))) as w_count,
    (SELECT COUNT(DISTINCT rule_id) 
       FROM ruleresult 
      WHERE code = '1.2' 
         AND result = 'P' 
         AND rule_id NOT IN 
         (SELECT rule_id 
            FROM ruleresult 
           WHERE code = '1.2' AND result IN ('V', 'W'))) as p_count ;


| v_count | w_count | p_count |
|---------|---------|---------|
|       1 |       1 |       0 |


Check it at SQLFiddle.



You can also have a different approach:


You first PIVOT your rules
You then decide, for every rule, whether the result that counts is V, W or P
You sum how many of them you have.


All together, this would be:

SELECT
    sum(is_v) AS v_count,
    sum(is_w) AS w_count,
    sum(is_p) AS p_count
FROM
(
    -- Computing whether we should consider V, W or P
    SELECT
        rule_id,
        case when count_v0 &gt; 0 then 1 else 0 end as is_v,
        case when count_w0 &gt; 0 and count_v0 = 0 then 1 else 0 end as is_w,
        case when count_p0 &gt; 0 and count_w0 = 0 and count_v0 = 0 then 1 else 0 end as is_p
    FROM
    (
        -- Pivoting V, W, P
        SELECT
            rule_id, 
            count(case when result='V' then 1 end) AS count_v0,    
            count(case when result='W' then 1 end) AS count_w0,
            count(case when result='P' then 1 end) AS count_p0
        FROM
            ruleresult
        WHERE
            code = '1.2'
        GROUP BY
            rule_id 
    ) AS s0 
) AS s1;


You can check this second option at RexTester. This second approach is (most probably) more efficient if your table is big, because it will only scan it once...