1 2 Previous Next 18 Replies Latest reply: Jan 24, 2013 11:06 PM by a_cute_person RSS

    help on model clause

    a_cute_person
      I have a query like as below

      select empno,deptno, sum(sal) over (partition by deptno) tot_sal from scott.emp;


      will give result like this

      EMPNO DEPTNO TOT_SAL
      ---------- ---------- ----------
      7934 10 8750
      7782 10 8750
      7839 10 8750
      7902 20 10875
      7876 20 10875
      7566 20 10875
      7369 20 10875
      7788 20 10875

      8 rows selected.

      but as you can see the total sal and deptno repeats , what i want is dept wise total salary as shown below

      EMPNO DEPTNO TOT_SAL
      ---------- ---------- ----------
      7934
      7782
      7839
      Total for deptno 10 = 8750
      7902
      7876
      7566
      7369
      7788
      Total for deptno 20 = 10875

      i want to use model clause and get the above result
        • 1. Re: help on model clause
          avish16
          try using rollup
          • 2. Re: help on model clause
            a_cute_person
            how ?
            • 3. Re: help on model clause
              Rahul_India
              read this for the most efficient way
              http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1793764100346222947
              • 4. Re: help on model clause
                asahide
                Hi

                Does this query work?
                select empno,deptno, sum(sal) over (partition by deptno) tot_sal from scott.emp group by rollup(empno, deptno);
                Regards,
                • 5. Re: help on model clause
                  Karthick_Arp
                  SQL> select case when empno_grouping = 0 then to_char(empno)
                    2              else 'Total of deptno ' || deptno || ' = ' ||  to_char(sal)
                    3         end emp_string
                    4    from (
                    5            select empno, deptno, sum(sal) sal, grouping_id(empno) empno_grouping
                    6              from emp
                    7            group by grouping sets((empno,deptno), (deptno))
                    8         )
                  SQL> /
                   
                  EMP_STRING
                  --------------------------------------------------------------------------------
                  7782
                  7839
                  Total of deptno 10 = 7450
                  7369
                  7566
                  7788
                  7876
                  Total of deptno 20 = 7875
                  7499
                  7521
                  7654
                  7698
                  7844
                  Total of deptno 30 = 8450
                   
                  14 rows selected.
                  • 6. Re: help on model clause
                    a_cute_person
                    the url suggested is of no use, tom is using break statement, which i am also aware of and i don't want to use break statement, i want to get output using model clause

                    i experimented with model clause like this but this throws error, don't know what is missing

                    select empno from scott.emp
                    model partition by (deptno) dimension by (empno)
                    measures (sal)
                    rules(
                    sal ['Total salary for dept'] = sum(sal) [any])

                    Edited by: 970426 on Jan 24, 2013 2:07 AM
                    • 7. Re: help on model clause
                      Rahul_India
                      970426 wrote:
                      the url suggested is of no use, tom is using break statement, which i am also aware of and i don't want to use break statement, i want to get output using model clause

                      i experimented with model clause like this but this throws error, don't know what is missing

                      select empno from scott.emp
                      model partition by (deptno) dimension by (empno)
                      measures (sal)
                      rules(
                      sal ['Total salary for dept'] = sum(sal) [any])

                      Edited by: 970426 on Jan 24, 2013 2:07 AM
                      Why you need to use modal clause only when there is a better approach than that.I dont see any reason apart from the learning scenario
                      • 8. Re: help on model clause
                        a_cute_person
                        >
                        Why you need to use modal clause only when there is a better approach than that.I dont see any reason apart from the learning scenario
                        to learn about model clause, how it works and how can i implement it

                        Edited by: 970426 on Jan 24, 2013 2:17 AM
                        • 9. Re: help on model clause
                          Rahul_India
                          970426 wrote:
                          Why you need to use modal clause only when there is a better approach than that.I dont see any reason apart from the learning scenario
                          to learn about model clause, how it works and how can i implement it

                          Edited by: 970426 on Jan 24, 2013 2:17 AM
                          Then you need to check this thread
                          Running total on the basis of condition

                          You haven't provided the DDL and DML so i can reproduce the result you want
                          • 10. Re: help on model clause
                            a_cute_person
                            >
                            You haven't provided the DDL and DML so i can reproduce the result you want
                            buddy its just an employee table available anywhere if you don't have it

                            CP this

                            CREATE TABLE SCOTT.EMP
                            (
                            EMPNO NUMBER(4),
                            ENAME VARCHAR2(10 BYTE),
                            JOB VARCHAR2(9 BYTE),
                            MGR NUMBER(4),
                            HIREDATE DATE,
                            SAL NUMBER(7,2),
                            COMM NUMBER(7,2),
                            DEPTNO NUMBER(2)
                            )


                            Insert into SCOTT.EMP
                            (EMPNO, ENAME, JOB, MGR, HIREDATE,
                            SAL, DEPTNO)
                            Values
                            (7369, 'SMITH', 'CLERK', 7902, TO_DATE('12/17/1980 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
                            800, 20);
                            Insert into SCOTT.EMP
                            (EMPNO, ENAME, JOB, MGR, HIREDATE,
                            SAL, COMM, DEPTNO)
                            Values
                            (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('02/20/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
                            1600, 300, 30);
                            Insert into SCOTT.EMP
                            (EMPNO, ENAME, JOB, MGR, HIREDATE,
                            SAL, COMM, DEPTNO)
                            Values
                            (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('02/22/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
                            1250, 500, 30);
                            Insert into SCOTT.EMP
                            (EMPNO, ENAME, JOB, MGR, HIREDATE,
                            SAL, DEPTNO)
                            Values
                            (7566, 'JONES', 'MANAGER', 7839, TO_DATE('04/02/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
                            2975, 20);
                            Insert into SCOTT.EMP
                            (EMPNO, ENAME, JOB, MGR, HIREDATE,
                            SAL, COMM, DEPTNO)
                            Values
                            (7654, 'MARTIN', 'SALESMAN', 7698, TO_DATE('09/28/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
                            1250, 1400, 30);
                            Insert into SCOTT.EMP
                            (EMPNO, ENAME, JOB, MGR, HIREDATE,
                            SAL, DEPTNO)
                            Values
                            (7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('05/01/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
                            2850, 30);
                            Insert into SCOTT.EMP
                            (EMPNO, ENAME, JOB, MGR, HIREDATE,
                            SAL, DEPTNO)
                            Values
                            (7782, 'CLARK', 'MANAGER', 7839, TO_DATE('06/09/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
                            2450, 10);
                            Insert into SCOTT.EMP
                            (EMPNO, ENAME, JOB, MGR, HIREDATE,
                            SAL, DEPTNO)
                            Values
                            (7788, 'SCOTT', 'ANALYST', 7566, TO_DATE('04/19/1987 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
                            3000, 20);
                            Insert into SCOTT.EMP
                            (EMPNO, ENAME, JOB, HIREDATE, SAL,
                            DEPTNO)
                            Values
                            (7839, 'KING', 'PRESIDENT', TO_DATE('11/17/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 5000,
                            10);
                            Insert into SCOTT.EMP
                            (EMPNO, ENAME, JOB, MGR, HIREDATE,
                            SAL, COMM, DEPTNO)
                            Values
                            (7844, 'TURNER', 'SALESMAN', 7698, TO_DATE('09/08/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
                            1500, 0, 30);
                            Insert into SCOTT.EMP
                            (EMPNO, ENAME, JOB, MGR, HIREDATE,
                            SAL, DEPTNO)
                            Values
                            (7876, 'ADAMS', 'CLERK', 7788, TO_DATE('05/23/1987 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
                            1100, 20);
                            Insert into SCOTT.EMP
                            (EMPNO, ENAME, JOB, MGR, HIREDATE,
                            SAL, DEPTNO)
                            Values
                            (7900, 'JAMES', 'CLERK', 7698, TO_DATE('12/03/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
                            950, 30);
                            Insert into SCOTT.EMP
                            (EMPNO, ENAME, JOB, MGR, HIREDATE,
                            SAL, DEPTNO)
                            Values
                            (7902, 'FORD', 'ANALYST', 7566, TO_DATE('12/03/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
                            3000, 20);
                            Insert into SCOTT.EMP
                            (EMPNO, ENAME, JOB, MGR, HIREDATE,
                            SAL, DEPTNO)
                            Values
                            (7934, 'MILLER', 'CLERK', 7782, TO_DATE('01/23/1982 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
                            1300, 10);
                            COMMIT;
                            • 11. Re: help on model clause
                              a_cute_person
                              i want to mark 1 useful reply as "Correct" who is that lucky person ???

                              Edited by: 970426 on Jan 24, 2013 3:24 AM
                              • 12. Re: help on model clause
                                chris227
                                select
                                *
                                from emp
                                model
                                partition by (deptno)
                                dimension by (row_number() over(
                                              partition by deptno
                                              order by empno) rn)
                                measures (sal)
                                rules(
                                sal[0]=sum(sal) [any]
                                )
                                order by
                                deptno, rn desc
                                • 13. Re: help on model clause
                                  a_cute_person
                                  chris227 wrote:
                                  select
                                  *
                                  from emp
                                  model
                                  partition by (deptno)
                                  dimension by (row_number() over(
                                  partition by deptno
                                  order by empno) rn)
                                  measures (sal)
                                  rules(
                                  sal[0]=sum(sal) [any]
                                  )
                                  order by
                                  deptno, rn desc
                                  bingo but where are the totals "Total for deptno" can i mark your reply as helpful ?
                                  • 14. Re: help on model clause
                                    chris227
                                    select
                                     deptno
                                    ,nvl(to_char(empno),'Total for deptno '||deptno)
                                     empno
                                    ,sal
                                    from emp
                                    model
                                    partition by (deptno)
                                    dimension by (row_number() over(
                                                  partition by deptno
                                                  order by empno) rn)
                                    measures (sal, empno)
                                    rules(
                                    sal[0]=sum(sal) [any]
                                    )
                                    order by
                                    deptno, empno
                                    1 2 Previous Next