conditional group by in Oracle

Question

I'm trying to write a query to get the sum of a table with condition. for example this table

area    |    machine    |    status    |    qty

1       |    machine1   |    inprocess |    210
1       |    machine2   |    pending   |    120
1       |    machine3   |    done      |    50
2       |    machine1   |    inprocess |    30
2       |    machine2   |    done      |    170

the result that I want would be

area  |  inprocess sum  |  pending sum   | done sum  |  all sum

1     |  210            |  120           | 50        |  380
2     |  30             |  0             | 170       |  200

I'm thinking about group by clauses with conditionals but I can't seem to get the solution that I need for this problem. What I tried was

select distinct 
    area,
    (select sum(qty) from table
     where status = 'inprocess'
     and area = mainTable.area) as inprocess sum
    (select sum(qty) from table
     where status = 'pending'
     and area = mainTable.area) as pending sum
    (select sum(qty) from table
     where status = 'done'
     and are = mainTable.area) as done sum
from table mainTable

up to a point, it does work but when I try to check if I'm getting the correct quantity I can't seem to match the actual value with the result of my query. (I'm working at a much larger data set, millions of records). I guess what I'm really after is a group by clause with which I can put conditions in so it will only aggregate records that would match the condition. Please shed light jedi masters

Answer 1

You can try using a pivot query:

SELECT t.area, SUM(t.isum) AS "inprocess sum",
               SUM(t.psum) AS "pending sum",
               SUM(t.dsum) AS "done sum",
               SUM(t.isum) + SUM(t.psum) + SUM(t.dsum) AS "all sum"
FROM
(
    SELECT area,
        CASE WHEN status = 'inprocess' THEN qty ELSE 0 END AS isum,
        CASE WHEN status = 'pending' THEN qty ELSE 0 END AS psum,
        CASE WHEN status = 'done' THEN qty ELSE 0 END AS dsum
    FROM mainTable
) t
GROUP BY t.area

If you are using Oracle 11g or higher, then you can also take advantage of the built-in PIVOT function:

SELECT area, isum_qty AS "inprocess sum", psum_qty AS "pending sum", dsum_qty AS "done sum",
    isum_qty + psum_qty + dsum_qty AS "all sum"
FROM
(
    SELECT area, status
    FROM mainTable
)
PIVOT
(
    SUM(qty) AS qty
    FOR status IN ('inprocess' AS "isum", 'pending' AS "psum", 'done' AS "dsum")
)
ORDER BY area ASC;

Answer 2

You should use PIVOT query as:

WITH pivot_data AS (
            SELECT area,status, qty from table
            )
    SELECT *
    FROM   pivot_data
    PIVOT (
              sum(qty)        --<-- pivot_clause
          FOR table --<-- pivot_for_clause

         IN  (FORM Hidden FIELD Name)    --<-- pivot_in_clause         
);

For Dynamic IN clause, create a form hidden filed and pass the following query result into this. Then refer that field to IN clause of the above query.

SELECT LISTAGG(dbms_assert.enquote_literal(status), ', ') WITHIN GROUP (ORDER BY status) status
FROM (Select distinct status from table)