Query to Summarize Statistics with Group By

Question

I have been looking over the web but have not come across an answer to my problem. The closest I found was this also from StackOverflow.

So what I have is transactions with different statuses. I am trying to compile a query that will summarize all transactions according to their status. I can easily do that with a Group By, but what I need and can’t do is to sum two of the particular statuses. For example:

Status 
--------
Status_A
Status_B
Status_C_D
Status_C
Status_D
Status_E

Status_C_D above would be the sum of Status_C and Status_D. In case you are wondering, status C is a kind of transaction error, and Status_D is another kind of error, so they want the sum of both to easily see the total of transactions with errors (everything will be shown on a report). What I have is this:

SELECT DECODE(STATUS_CD, 
         0, 'Status_A',
         1, 'Status_B',
         2, 'Status_C',
         3, 'Status_D',
         4, 'Status_E',
         'Unknown') status
FROM table_a ...

Hopefully that's enough context. Any help is much appreciated. :)

select decode(TABLE_A.status_cd, 0,  'Status A',
                                 1, 'Status B',
                                 2, 'Status C',
                                 3, 'Status D',
                                 4, 'Status E',
                                 'Unknown') status, 
        SUM(decode(trunc(((sysdate - TABLE_A.date_time) +8) / 8), 1, 1, 0)) "0-7",
        SUM(decode(trunc(((sysdate - TABLE_A.date_time) +8) / 8), 2, 1, 0)) "8-15",
        SUM(decode(trunc(((sysdate - TABLE_A.date_time) +8) / 8), 3, 1, 0)) "16-23",
        SUM(decode(trunc(((sysdate - TABLE_A.date_time) +9) / 8), 4, 1, 0)) "24-30",
        SUM(decode(trunc(((sysdate - TABLE_A.date_time) -1) / 30), 1, 1, 0)) "31-60",
        SUM(decode(trunc(((sysdate - TABLE_A.date_time) -1) / 30), 2, 1, 0)) "61-90",
        SUM(decode(trunc(((sysdate - TABLE_A.date_time) -1) / 30), 3, 1, 0)) "91-120",
        SUM(decode(trunc(((sysdate - TABLE_A.date_time) -1) / 60), 2, 1, 0)) "121-180",
        SUM(decode(trunc(((sysdate - TABLE_A.date_time)) / 181), 0, 0, 1)) ">180"
FROM Table_A
WHERE ...
GROUP BY TABLE_A.status_cd

We're using 10g and as far as I know, they won't switch for another few years. Thanks. — Mo_Joe, Jan 18 '12 at 17:08

score 0 · Accepted Answer · 2012-01-18T19:00:02.463

0

Assuming you are happy to see these summaries across the page, the simplest way to do this would be:

select sum(DECODE(STATUS_CD, 0,1, 0)) Status_A,
       sum(DECODE(STATUS_CD, 1,1, 0)) Status_B,
       sum(DECODE(STATUS_CD, 2,1, 3,1, 0)) Status_C_D,
       sum(DECODE(STATUS_CD, 2,1, 0)) Status_C,
       sum(DECODE(STATUS_CD, 3,1, 0)) Status_D,
       sum(DECODE(STATUS_CD, 4,1, 0)) Status_E
FROM table_a ...

EDIT: If you need to see the results down the page, try:

select "status", 
        SUM(decode(trunc(((sysdate - "date_time") +8) / 8), 1, 1, 0)) "0-7",
        SUM(decode(trunc(((sysdate - "date_time") +8) / 8), 2, 1, 0)) "8-15",
        SUM(decode(trunc(((sysdate - "date_time") +8) / 8), 3, 1, 0)) "16-23",
        SUM(decode(trunc(((sysdate - "date_time") +9) / 8), 4, 1, 0)) "24-30",
        SUM(decode(trunc(((sysdate - "date_time") -1) / 30), 1, 1, 0)) "31-60",
        SUM(decode(trunc(((sysdate - "date_time") -1) / 30), 2, 1, 0)) "61-90",
        SUM(decode(trunc(((sysdate - "date_time") -1) / 30), 3, 1, 0)) "91-120",
        SUM(decode(trunc(((sysdate - "date_time") -1) / 60), 2, 1, 0)) "121-180",
        SUM(decode(trunc(((sysdate - "date_time")) / 181), 0, 0, 1)) ">180"
from
(SELECT DECODE(coalesce(d.dummy_status, a.STATUS_CD), 
         'X', 'Status_C_D'
         '0', 'Status_A',
         '1', 'Status_B',
         '2', 'Status_C',
         '3', 'Status_D',
         '4', 'Status_E',
         'Unknown') "status",
         a."date_time"
 FROM table_a a
 LEFT JOIN (select '2' status_cd, '2' dummy_status from dual union
            select '2' status_cd, 'X' dummy_status from dual union
            select '3' status_cd, '3' dummy_status from dual union
            select '3' status_cd, 'X' dummy_status from dual) d
        ON a.status_cd = d.status_cd
 ...
) group by "status"

edited Jan 18 '12 at 19:00

answered Jan 18 '12 at 15:36

Hmm not quite, because this gives me all the statuses on the X axis, and I need them on the Y axis as summarized records. But thanks! – Mo_Joe Jan 18 '12 at 15:50
@Mo_Joe: I have amended my answer to include a query which should return the results down the page. – Jan 18 '12 at 16:13
Ok, I will try this and post back the results. – Mo_Joe Jan 18 '12 at 16:18
Mark, I tried this but it may be a little too much for my expertise. I could not get it to run (although I didn't try long enough). I may give it another shot in a few minutes. Thanks. – Mo_Joe Jan 18 '12 at 16:59
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR Its selecting that first a.STATUS_CD – Mo_Joe Jan 18 '12 at 17:06
@Mo_Joe: What datatype is table_a.status_cd? – Jan 18 '12 at 17:14
Just came back to this and did a cast on a.STATUS_CD (it was varchar2) and it works. However when I try adding the other summaries (see edited post above) it throws: ORA-00904: "A"."DATE_TIME": invalid identifier ...Thanks for your help so far Mark! – Mo_Joe Jan 18 '12 at 18:20
@Mo_Joe: Try now. (Note that date_time may be a reserved word in Oracle, which is why I have double-quoted it; this should be an exact match of the defined name on the table, including upper/lower-case, so if the column is actually declared as "Date_Time" you will need to amend it throughout the query to match.) – Jan 18 '12 at 19:02
*sigh* Now it keeps throwing a "missing right parenthesis" error. I've looked through the query several times but can't find any missing parenths. After I OK the error it usually selects the second dummy_status of the LEFT JOIN. Any suggestions? – Mo_Joe Jan 18 '12 at 19:47
I can't see anywhere that a parenthesis might be missing, unless it is related to the ellipsis `...` near the end. Do you get the same error if you just highlight the query within the outermost parentheses - ie. `SELECT DECODE ... ON a.status_cd = d.status_cd` ? – Jan 18 '12 at 20:08
1

Mike, I finally got it to work (it was a missing comma after Status_C_D!!) also had to remove the double quotes from date_time as it was complaining. This is working great now, just what I needed! Thanks so much!!!! – Mo_Joe Jan 19 '12 at 16:05

Olivier Jacot-Descombes · Answer 2 · 2012-01-18T16:30:15.920

0

SELECT Status, SUM(other_column) AS other_sum
FROM
    (SELECT
        CASE WHEN STATUS_CD = 'Status_C' OR STATUS_CD = 'Status_D'
            THEN 'Status_C_D'
            ELSE STATUS_CD
        END AS Status,
        other_column
    FROM
        table_a) A
GROUP BY
    Status

EDIT: Try by only changing your DECODE slightly:

SELECT status, SUM(other_column) AS other_sum
FROM
    (SELECT
        DECODE(STATUS_CD,  
               0, 'Status_A', 
               1, 'Status_B', 
               2, 'Status_C_D', 
               3, 'Status_C_D', 
               4, 'Status_E', 
               'Unknown') status, 
        other_column
    FROM
        table_a) A
GROUP BY
    status

STATUS_CD 2 and 3 now produce the same text 'Status_C_D' and will result in a single column in a corresponding pivot table.

(My first code exmample was wrong, since STATUS_CD is a number, which you convert to a text.)

edited Jan 18 '12 at 16:30

answered Jan 18 '12 at 15:45

Olivier Jacot-Descombes

104,806
13
138
188

But won't this still give me two columns? I'll try it and post back. Thanks. – Mo_Joe Jan 18 '12 at 16:18
"Status_C" and "Status_D" are replace by a new status code "Status_C_D" and will generate a single column when pivoted. – Olivier Jacot-Descombes Jan 18 '12 at 16:23
I got your last edit, but now status_c and status_d are not displayed (and they want them displayed individually too). I will edit my post and include the entire query to see if that helps too. Thanks so much! – Mo_Joe Jan 18 '12 at 16:57
SELECT *, Status_C + Status_D AS Status_C_D FROM your_pivot_query. – Olivier Jacot-Descombes Jan 18 '12 at 18:14

Query to Summarize Statistics with Group By

2 Answers2