CODEWITHSUNDEEP
databaseoracle-databaseplsqlaggregate
Deltax76
Deltax76

Reputation: 14263

How to summarize in PL/SQL

Given that I have a table contains user data, like this:

userID     calltime     result 
1         10:20         1
1         11:00         2
1         11:30         1
2         9:30          1
2         11:00         1
3         10:00         1
3         10:30         2
3         11:00         1
3         11:30         2

Now I want to summarize it like that:

userID     result1    result2
1             2         1
2             2         0
3             2         2
(total)       6         3

for me, summarizing user data is fine, but how can I add total row in a query? UNION does not work with this.

Thank you so much for this.

EDIT: this my current query, of course, it does not work:

  PROCEDURE P_SUMMARIZE_CALL
  (
    pStartDate IN DATE,
    pEndDate IN DATE,
    SummaryCur  OUT MEGAGREEN_CUR    
  )
  IS
  BEGIN
    OPEN SUMMARYCUR FOR
    SELECT USERID, TOTALCALLS,CONNECTEDCALLS,RATE,NOANSWER FROM
    (((SELECT USERID,
            count(CALLID) AS TOTALCALLS, 
            sum(CONNECTED) as CONNECTEDCALLS, 
            sum(CONNECTED)/count(CALLID)*100 || '%' AS RATE,
            (count(CALLID) - sum(CONNECTED)) AS NOANSWER 
    FROM CALLLOGS
    WHERE STARTTIME BETWEEN pStartDate AND pEndDate  
   group by USERID) c
   FULL OUTER JOIN USERS u
   ON c.USERID = u.ID)

    UNION ALL 
     (SELECT NULL,count(CALLID) AS TOTALCALLS, 
            sum(CONNECTED) as CONNECTEDCALLS, 
            sum(CONNECTED)/count(CALLID)*100 || '%' AS RATE,
            (count(CALLID) - sum(CONNECTED)) AS NOANSWER 
    FROM CALLLOGS
    WHERE STARTTIME BETWEEN pStartDate AND pEndDate group by NULL));   

  END;

Upvotes: 0

Views: 691

Answers (2)

Modafuka
Modafuka

Reputation: 31

There is a bit faster way of doing this...

Assuming you can only get a value of 1 or 2 on the field "result", this would work:

SELECT  userid
,       SUM(DECODE (result, 1, 1, 0)) as result1
,       SUM(DECODE (result, 2, 1, 0)) as result2
FROM    x
GROUP BY userid;

This would generate the expected output:

USERID    RESULT1   RESULT2                
--------- --------- ---------
1         2         1
2         2         0 
3         2         2

If the possible values on the field result are more than 1 or 2, you could add more sum-decode rows, or do the aforementioned PL/SQL procedure.

Upvotes: 1

Mark Byers
Mark Byers

Reputation: 838896

I assume you are using GROUP BY to generate your current result. (PS: Please post your current query as it is rather hard to guess what you are doing to get that result).

To get what you want use GROUP BY ROLLUP instead of GROUP BY.

UNION does not work with this.

Yes, actually that will work too. But I'd suggest UNION ALL rather than UNION as your result set seems to already to contain only distinct rows so there is no point using extra calculation time searching for duplicate rows to remove.

Upvotes: 5

Related Questions