SQL Query – How to apply different conditions (Where clause?) to specific aggregated columns

I have a table (simplified example below) consisting of TEST(s), TESTPRICE per test and TESTSTATUS. I would like to be able to run a query that will result in two aggregated columns, one for pending revenue and one for started revenue per test group.

I can easily generate two seperate result tables by executing the following:

SELECT
    Test
    ,SUM(TESTPRICE) AS 'PendingRev'
FROM
    REQUEST
WHERE
    TESTSTATUS = 'P'
GROUP BY
    TEST
ORDER BY
    TEST
SELECT
    Test
    ,SUM(TESTPRICE) AS 'StartedRev'
FROM
    REQUEST
WHERE
    TESTSTATUS = 'S'
GROUP BY
    TEST
ORDER BY
    TEST

This results in two out put results like this:

However, I would prefer a single output result table like this:

Can anyone point me in the right direction here, advise on a technique I can read up on – I’ve tried a few different approaches (subqueries, etc.) but the closest I seem to get is a total sum for a given TESTSTATUS in all rows, or errors

Any help is appreciated

Answer

Using conditional aggregation, which in most databases looks like this:

SELECT Test, 
       SUM(CASE WHEN TESTSTATUS = 'P' THEN TESTPRICE END) AS PendingRev,
       SUM(CASE WHEN TESTSTATUS = 'S' THEN TESTPRICE END) AS StartedRev
FROM REQUEST
GROUP BY TEST
ORDER BY TEST;

Advertisement

Answer