Home » SQL & PL/SQL » SQL & PL/SQL » Grouping
Grouping [message #263295] Wed, 29 August 2007 11:01 Go to next message
konkanb
Messages: 1
Registered: August 2007
Junior Member
Hi Folks,
Hope you can assist me.Am trying to perform a specific type of grouping. Hope you can help. Example:
Select count(*) C.Cust_id, C.Cust_Nam, C.Cust_Add, C.Cust_City, Cust_Contact,
O.Order_Dt, O.Order_CompletedDt, O.SalesPerson
from Cust C, Order O
Where C.Cust_id = O.Cust_id
and O.ComplDt between date1 and date2
group by grouping sets(O.SalesPerson, (C.Cust_id,C.Cust_Nam, C.Cust_Add, C.Cust_City, Cust_Contact, O.Order_Dt, O.Order_CompletedDt))

Raw Data:
123 Joe 123 Main St Tokyo 1234567890 01/01/2007 01/20/2007 Sam
546 John 234short st Dallas 9874563214 01/10/2007 01/15/2007 Bob
658 Bill 758First St London 2458745929 01/22/2007 01/25/2007 Sam
742 Ted 651One St Paris 6741588634 01/25/2007 01/31/2007 Mike
874 Al 547 Main St Lisbon 4127589635 01/25/2007 01/30/2007 Mike

I would like the above detailed data, and then have it grouped by all orders that each sales person completed and the count of the jobs by each Salesperson.

Result should be similar to:
123 Joe 123 Main St Tokyo 1234567890 01/01/2007 01/20/2007 Sam
658 Bill 758First St London 2458745929 01/22/2007 01/25/2007 Sam
Total: Sam: 2

546 John 234short st Dallas 9874563214 01/10/2007 01/15/2007 Bob
Total: Bob: 1


742 Ted 651One St Paris 6741588634 01/25/2007 01/31/2007 Mike
874 Al 547 Main St Lisbon 4127589635 01/25/2007 01/30/2007 Mike
Total: Mike:2

Grand Total 5

Tks
Konkan
Re: Grouping [message #263298 is a reply to message #263295] Wed, 29 August 2007 11:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Post a valid test case (create table and insert statements, verify they are correct) on which we can work.

Regards
Michel
Re: Grouping [message #263322 is a reply to message #263295] Wed, 29 August 2007 13:04 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
Try a ROLLUP with GROUPING_ID. For example, using the EMP table, I came up with something similar to what you were requesting.

SELECT DECODE(GROUPING_ID(mgr, ename, job, hiredate, empno)
             , 0, TO_CHAR(empno)
             , 15, 'Total For ' || mgr
             , 31, 'Grand Total') empno
     , ename
     , job
     , hiredate
     , DECODE(GROUPING_ID(mgr, ename, job, hiredate, empno)
             , 0, mgr
             , 15, COUNT(*)
             , 31, COUNT(*)) mgr_cnt
FROM   emp
GROUP  BY ROLLUP(mgr, ename, job, hiredate, empno)
HAVING GROUPING_ID(mgr, ename, job, hiredate, empno) IN (0, 15, 31)
ORDER BY mgr;

EMPNO           ENAME      JOB       HIREDATE     MGR_CNT
--------------- ---------- --------- --------- ----------
7902            FORD       ANALYST   03-DEC-81       7566
7788            SCOTT      ANALYST   19-APR-87       7566
Total For 7566                                          2
7499            ALLEN      SALESMAN  20-FEB-81       7698
7900            JAMES      CLERK     03-DEC-81       7698
7654            MARTIN     SALESMAN  28-SEP-81       7698
7844            TURNER     SALESMAN  08-SEP-81       7698
7521            WARD       SALESMAN  22-FEB-81       7698
Total For 7698                                          5
7934            MILLER     CLERK     23-JAN-82       7782
Total For 7782                                          1
7876            ADAMS      CLERK     23-MAY-87       7788
Total For 7788                                          1
7698            BLAKE      MANAGER   01-MAY-81       7839
7782            CLARK      MANAGER   09-JUN-81       7839
7566            JONES      MANAGER   02-APR-81       7839
Total For 7839                                          3
7369            SMITH      CLERK     17-DEC-80       7902
Total For 7902                                          1
7839            KING       PRESIDENT 17-NOV-81
Total For                                               1
Grand Total                                            14
Re: Grouping [message #263332 is a reply to message #263322] Wed, 29 August 2007 13:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or to avoid useless calculations:
SQL> SELECT DECODE(GROUPING_ID(mgr, ename, job, hiredate, empno)
  2               , 0, TO_CHAR(empno)
  3               , 15, 'Total For ' || mgr
  4               , 31, 'Grand Total') empno
  5       , ename
  6       , job
  7       , hiredate
  8       , DECODE(GROUPING_ID(mgr, ename, job, hiredate, empno)
  9               , 0, mgr
 10               , 15, COUNT(*)
 11               , 31, COUNT(*)) mgr_cnt
 12  FROM   emp
 13  GROUP  BY ROLLUP (mgr, (ename, job, hiredate, empno))
 14  ORDER BY mgr;
EMPNO           ENAME      JOB       HIREDATE               MGR_CNT
--------------- ---------- --------- ------------------- ----------
7902            FORD       ANALYST   03/12/1981 00:00:00       7566
7788            SCOTT      ANALYST   19/04/0087 00:00:00       7566
Total For 7566                                                    2
7499            ALLEN      SALESMAN  20/02/1981 00:00:00       7698
7900            JAMES      CLERK     03/12/1981 00:00:00       7698
7654            MARTIN     SALESMAN  28/09/1981 00:00:00       7698
7844            TURNER     SALESMAN  08/09/1981 00:00:00       7698
7521            WARD       SALESMAN  22/02/1981 00:00:00       7698
Total For 7698                                                    5
7934            MILLER     CLERK     23/01/1982 00:00:00       7782
Total For 7782                                                    1
7876            ADAMS      CLERK     23/05/0087 00:00:00       7788
Total For 7788                                                    1
7698            BLAKE      MANAGER   01/05/1981 00:00:00       7839
7782            CLARK      MANAGER   09/06/1981 00:00:00       7839
7566            JONES      MANAGER   02/04/1981 00:00:00       7839
Total For 7839                                                    3
7369            SMITH      CLERK     17/12/1980 00:00:00       7902
Total For 7902                                                    1
7839            KING       PRESIDENT 17/11/1981 00:00:00
Total For                                                         1
Grand Total                                                      14

22 rows selected.

Regards
Michel
Re: Grouping [message #263335 is a reply to message #263295] Wed, 29 August 2007 14:00 Go to previous message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
Very nice...I didn't know you could do that!
Previous Topic: what are all the steps to take snapshot in oracle 9i ?
Next Topic: oracle datatye
Goto Forum:
  


Current Time: Sun Dec 04 10:59:02 CST 2016

Total time taken to generate the page: 0.10245 seconds