CODEWITHSUNDEEP
mysqlgroup-bymariadbrollup
luckyme
luckyme

Reputation: 1

Rollup grouped rows in mariadb

My data is like this ; the final status has to be computed for each hostname ,control_id -pass if status is passed for all control_id for that hostname 

----------
hostname    control_id  status
abc standard    pass
abc standard    fail
abc premium pass
abc premium pass
abc classic fail
abc classic fail
xyz standard    pass
xyz standard    fail
xyz premium pass
xyz premium pass
xyz classic fail
xyz classic fail

   I am not able to write a proper query

Upvotes: 0

Views: 112

Answers (1)

Rick James
Rick James

Reputation: 142296

SELECT  hostname, control_id,
        MIN(status) AS overall_status
    GROUP BY hostname, control_id

That is a kludge. It assumes that "fail" < "pass".

If, in my first query, you want only the failing ones, tack on this clause:

    HAVING overall_status = 'fail'

There are more complex ways, probably involving a self-join with LEFT and/or NOT EXISTS. Stroke my ego, and I will wave my magic wand to conjure up such.

Upvotes: 1

Related Questions