|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to get this SQL output :- [message #598212 is a reply to message #598206] |
Fri, 11 October 2013 08:55   |
 |
jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum
|
Senior Member |
|
|
Michel as you know earlier i was not aware about this topic , i faced this question in interview and asked here for help , after coming here all i know about analytic function is that it computes aggregate values based on group of rows , it is little bit similiar to group by functions , but it returns multiple row output for each group and partition by clause starts executing after order by, where etc clause till yet i know this.
and i am attaching the scan copy of that question in attachments , kindly check it , i may be wrong but you guys can clarify me is my question is right or not or is there any mistake in printing. Thanks
-
Attachment: 01.jpg
(Size: 33.17KB, Downloaded 553 times)
[Updated on: Fri, 11 October 2013 08:56] Report message to a moderator |
|
|
|
|
|
| Re: How to get this SQL output :- [message #598220 is a reply to message #598218] |
Fri, 11 October 2013 09:23 |
|
jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum
|
Senior Member |
|
|
so there is printing mistake in question paper is it confirm?
and
now found one more thing while searching about partition by clause
SELECT t.sno, t.NAME, t.country,
SUM (t.amount) OVER (order BY country) AS amount
FROM TEST t
ORDER BY country DESC, sno DESC
output:-
SNO NAME COUNTRY AMOUNT
--- ---------- ---------- ----------
5 E I 140
3 C I 140
1 A I 140
4 D A 60
2 B A 60
Now which topic need to be read to know about this clause (kindly provide exact topic name like analytic functions for partition by clause)
and how it is different from partition by clause and why it is giving this output?
[Updated on: Fri, 11 October 2013 09:24] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: How to get this SQL output :- [message #598245 is a reply to message #597960] |
Fri, 11 October 2013 13:18 |
|
jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum
|
Senior Member |
|
|
tried few queries using same clause and finally got the concept :-
for ex :-
>select e.*,sum(e.sal) over (partition by deptno) as Dept_Tsal from emp e ;
gives output:-
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPT_TSAL
------ ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2550 10 9050
7934 MILLER CLERK 7782 23-JAN-82 1400 10 9050
7839 KING PRESIDENT 17-NOV-81 5100 10 9050
7902 FORD ANALYST 7566 03-DEC-81 3100 20 11475
7788 SCOTT ANALYST 7566 09-DEC-82 3200 100 20 11475
7566 JONES MANAGER 7839 02-APR-81 3075 20 11475
7876 ADAMS CLERK 7788 12-JAN-83 1200 20 11475
7369 SMITH CLERK 7369 17-DEC-80 900 20 11475
7499 ALLEN SALESMAN 7698 20-FEB-81 1700 300 30 10250
7521 WARD SALESMAN 7698 22-FEB-81 1600 500 30 10250
7900 JAMES CLERK 7698 03-DEC-81 1050 30 10250
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPT_TSAL
------ ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
7698 BLAKE MANAGER 7839 01-MAY-81 2950 30 10250
7654 MARTIN SALESMAN 7698 28-SEP-81 1350 1400 30 10250
7844 TURNER SALESMAN 7698 08-SEP-81 1600 0 30 10250
Note:- here it is sorting the data according to deptno , then it is showing the sum od sal of each department i.e sum(sal) for deptno 10 = 9050, for deptno 20= 11475 and for deptno 30=10250 and showing the sum of sal for each department multiple times.
Now 2nd query using "Order by"
>select e.*,sum(e.sal) over (order by deptno) as Dept_Tsal from emp e;
gives output:-
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPT_TSAL
------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2550 10 9050
7934 MILLER CLERK 7782 23-JAN-82 1400 10 9050
7839 KING PRESIDENT 17-NOV-81 5100 10 9050
7902 FORD ANALYST 7566 03-DEC-81 3100 20 20525
7788 SCOTT ANALYST 7566 09-DEC-82 3200 100 20 20525
7566 JONES MANAGER 7839 02-APR-81 3075 20 20525
7876 ADAMS CLERK 7788 12-JAN-83 1200 20 20525
7369 SMITH CLERK 7369 17-DEC-80 900 20 20525
7499 ALLEN SALESMAN 7698 20-FEB-81 1700 300 30 30775
7521 WARD SALESMAN 7698 22-FEB-81 1600 500 30 30775
7900 JAMES CLERK 7698 03-DEC-81 1050 30 30775
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPT_TSAL
------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
7698 BLAKE MANAGER 7839 01-MAY-81 2950 30 30775
7654 MARTIN SALESMAN 7698 28-SEP-81 1350 1400 30 30775
7844 TURNER SALESMAN 7698 08-SEP-81 1600 0 30 30775
Note:- in this query everything is same as "partition by" clause but it gives the sum(sal) for deptno 10= 9050
sum(sal) for deptno 20= sum(sal) of deptno10 i.e 9050 + sum(sal) for deptno 20 i.e 11475 => 20525
and sum(Sal) for deptno 30 => 9050+11475+2050 i.e 30775.
Note:- still didn't get any stuff to read much about this clause, made myself understand by running few queries, if still something is left, which i should know about this "order by" clause then please expalin, although got enough knowledge about partition clause .
Thanks everyone , Thanks Michel 
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
