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

Tim Biegeleisen · Accepted Answer · 2015-12-17T06:33:51.280

5

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;

edited Dec 17 '15 at 06:33

answered Dec 17 '15 at 05:58

Tim Biegeleisen

502,043
27
286
360

hi Tim, took me a while to grasp on the concept of PIVOT. but would you say using pivot is more effective than using an in memory temp table like the first block you wrote there? I wrote 2 sets of queries, one for each of the samples you gave for my problem. the explain plan suggests that using the PIVOT function is faster because it took 4 steps compared to with the using pivot query, which took 13 steps. – chip Jan 26 '16 at 07:36
but when I run both queries, first method and second method multiple times , it was consistent that the pivot query would take 2 seconds and the PIVOT function using query would take 3 seconds, so the 11g function makes the query slower? I know I have to further check this by using trace tools to really know whats going on, but what do you think is happening there? – chip Jan 26 '16 at 07:38
1

I'm not sure about the performance of `PIVOT`, but I would advise you to use which ever version with which you are most comfortable. Obviously, if performance is a concern of yours, the choose whatever performs the best. – Tim Biegeleisen Jan 26 '16 at 07:40
1

One more comment for you: The first query I gave you (the one _without_ `PIVOT`) is ANSI-92 compliant. This means that it will work, in theory, on any major database (e.g. Oracle, SQL Server, MySQL). However, the `PIVOT` function is specific to Oracle, so if you were to ever change your database you would have to rewrite the query. – Tim Biegeleisen Jan 26 '16 at 08:03

Muhammad Muazzam · Answer 2 · 2015-12-17T09:31:51.587

1

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)

edited Dec 17 '15 at 09:31

answered Dec 17 '15 at 08:30

Muhammad Muazzam

2,810
6
33
62

1

What's a "form hidden field"? Where did the OP mention that they are developing some kind of GUI or Web UI? For all we know, this might be a report that is executed as a script from SQL/Plus. – Frank Schmitt Dec 17 '15 at 09:53
@Frank Its optional, if someone want to use dynamic IN clause otherwise they have to put static values. – Muhammad Muazzam Dec 17 '15 at 10:03

conditional group by in Oracle

2 Answers2