SELECT with multiple COUNTs

oracle, sql

Solution

You do it like this using SQL grouping, along with the COUNT() and SUM() aggregate functions. For SUM we use a standard SQL "trick" of an embedded CASE statement.

select GRP, COUNT(*) as Total, 
            SUM(CASE WHEN STATUS = 'Pass' THEN 1 ELSE 0 END) AS Pass,
            SUM(CASE WHEN STATUS = 'Fail' THEN 1 ELSE 0 END) AS Fail
from table
group by GRP

Average Use the same tricks to get average knowing that the AVG aggregate will ignore any parameter which is null.

select GRP, COUNT(*) as Total, 
            SUM(CASE WHEN STATUS = 'Pass' THEN 1 ELSE 0 END) AS Pass,
            SUM(CASE WHEN STATUS = 'Fail' THEN 1 ELSE 0 END) AS Fail,
            AVG(CASE WHEN STATUS = 'Pass' THEN Score ELSE null END) AS PassAVG,
            AVG(CASE WHEN STATUS = 'Fail' THEN Score ELSE null END) AS FailAVG,
from table
group by GRP

Problem

In Oracle, given the following data ``` +------------+-----+ + STATUS | GRP + +------------+-----+ + Pass | A + + Fail | A + + Pass | A + + Pass | B + + Fail | B + + Pass | C + + bad | C + +------------------+ ``` I would like to get the following result ``` +---------+-------+-------+-------+ + GRP | Total + Pass + Fail + +---------+-------+-------+-------+ + A | 3 + 2 + 1 + + B | 2 + 1 + 1 + + C | 2 + 1 + 0 + +---------+-------+-------+-------+ ``` Is it possible to do it with one SQL query or do I need to make three separate SQL calls?

Original source