Home » SQL & PL/SQL » SQL & PL/SQL » How to get this SQL output :- (Sql Plus 10g)
How to get this SQL output :- [message #597960] Wed, 09 October 2013 07:54 Go to next message
jgjeetu
Messages: 240
Registered: July 2013
Location: India Delhi Rohini Sec-1
Senior Member

Q1).I have one table emp as:-

S.NO   NAME   COUNTRY    AMOUNT
1       A       I          10
2       B       A          20
3       C       I          30
4       D       A          40
5       E       I          40


AND I REQUIRE OUTPUT AS:-
S.NO   NAME   COUNTRY    AMOUNT
5        A       I         80
3        C       I         80
1        A       I         80
4        D       A         60
2        B       A         60

Re: How to get this SQL output :- [message #597962 is a reply to message #597960] Wed, 09 October 2013 07:57 Go to previous messageGo to next message
cookiemonster
Messages: 11203
Registered: September 2008
Location: Rainy Manchester
Senior Member
Use sum in its analytic form.
icon3.gif  Re: How to get this SQL output :- [message #597965 is a reply to message #597960] Wed, 09 October 2013 08:06 Go to previous messageGo to next message
Michel Cadot
Messages: 59814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> select ename, job, sal, deptno, 
  2         sum(sal) over (partition by deptno) sum_sal
  3  from emp
  4  order by deptno
  5  /
ENAME      JOB              SAL     DEPTNO    SUM_SAL
---------- --------- ---------- ---------- ----------
CLARK      MANAGER         2450         10       8750
KING       PRESIDENT       5000         10       8750
MILLER     CLERK           1300         10       8750
SMITH      CLERK            800         20      10875
JONES      MANAGER         2975         20      10875
SCOTT      ANALYST         3000         20      10875
ADAMS      CLERK           1100         20      10875
FORD       ANALYST         3000         20      10875
ALLEN      SALESMAN        1600         30       9400
WARD       SALESMAN        1250         30       9400
MARTIN     SALESMAN        1250         30       9400
BLAKE      MANAGER         2850         30       9400
TURNER     SALESMAN        1500         30       9400
JAMES      CLERK            950         30       9400

Re: How to get this SQL output :- [message #597972 is a reply to message #597965] Wed, 09 October 2013 08:32 Go to previous messageGo to next message
jgjeetu
Messages: 240
Registered: July 2013
Location: India Delhi Rohini Sec-1
Senior Member

Thanks michel and coockiemonster , but i have not read this topic so i am not able to understand the concept , can you please provide me the link where i could read and understand this topic please
icon1.gif  Re: How to get this SQL output :- [message #597974 is a reply to message #597972] Wed, 09 October 2013 08:33 Go to previous messageGo to next message
Michel Cadot
Messages: 59814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Database SQL Reference

Re: How to get this SQL output :- [message #597981 is a reply to message #597974] Wed, 09 October 2013 08:59 Go to previous messageGo to next message
jgjeetu
Messages: 240
Registered: July 2013
Location: India Delhi Rohini Sec-1
Senior Member

Searched partition over , sum in analytical function , group functions also but didn't get the right topic ? please help

[Updated on: Wed, 09 October 2013 08:59]

Report message to a moderator

icon2.gif  Re: How to get this SQL output :- [message #597983 is a reply to message #597981] Wed, 09 October 2013 09:16 Go to previous messageGo to next message
Michel Cadot
Messages: 59814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In the link I gave just search "Analytic Functions" section.

Re: How to get this SQL output :- [message #597984 is a reply to message #597983] Wed, 09 October 2013 09:20 Go to previous messageGo to next message
jgjeetu
Messages: 240
Registered: July 2013
Location: India Delhi Rohini Sec-1
Senior Member

now got the right topic to read , thanks Smile
Re: How to get this SQL output :- [message #598196 is a reply to message #597984] Fri, 11 October 2013 06:59 Go to previous messageGo to next message
jgjeetu
Messages: 240
Registered: July 2013
Location: India Delhi Rohini Sec-1
Senior Member

My Coding:-
SELECT   t.sno, t.NAME, t.country,
         SUM (t.amount) OVER (PARTITION BY country) AS amount
    FROM TEST t
ORDER BY country DESC, sno DESC


Output i get:-

 SNO NAME       COUNTRY        AMOUNT
---- ---------- ---------- ----------
   5 E          I                  80
   3 C          I                  80
   1 A          I                  80
   4 D          A                  60
   2 B          A                  60


Required Output is:-
S.NO   NAME   COUNTRY    AMOUNT
5        A       I         80
3        C       I         80
1        A       I         80
4        D       A         60
2        B       A         60


Tried a lot but couldn't able to get correct output , still having problem in name column , Please help
Re: How to get this SQL output :- [message #598199 is a reply to message #598196] Fri, 11 October 2013 07:20 Go to previous messageGo to next message
pablolee
Messages: 2655
Registered: May 2007
Location: Scotland
Senior Member
Why should it be
A
C
A
when your source data is
A I
C I
E I

icon5.gif  Re: How to get this SQL output :- [message #598206 is a reply to message #598196] Fri, 11 October 2013 08:45 Go to previous messageGo to next message
Michel Cadot
Messages: 59814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Tried a lot but couldn't able to get correct output ,


Can you explain with words your output.

Re: How to get this SQL output :- [message #598212 is a reply to message #598206] Fri, 11 October 2013 08:55 Go to previous messageGo to next message
jgjeetu
Messages: 240
Registered: July 2013
Location: India Delhi Rohini Sec-1
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 63 times)

[Updated on: Fri, 11 October 2013 08:56]

Report message to a moderator

Re: How to get this SQL output :- [message #598218 is a reply to message #598212] Fri, 11 October 2013 09:09 Go to previous messageGo to next message
cookiemonster
Messages: 11203
Registered: September 2008
Location: Rainy Manchester
Senior Member
Looks to me like there's a typo in the expected result and the result you are currently getting is actually the correct one.
Re: How to get this SQL output :- [message #598220 is a reply to message #598218] Fri, 11 October 2013 09:23 Go to previous messageGo to next message
jgjeetu
Messages: 240
Registered: July 2013
Location: India Delhi Rohini Sec-1
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 #598221 is a reply to message #598220] Fri, 11 October 2013 09:41 Go to previous messageGo to next message
cookiemonster
Messages: 11203
Registered: September 2008
Location: Rainy Manchester
Senior Member
click on the link Michel posted above.
Search that page for analytic functions.
click on that link.
read.
Re: How to get this SQL output :- [message #598237 is a reply to message #598220] Fri, 11 October 2013 12:07 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2520
Registered: May 2013
Location: World Wide on the Web
Senior Member
jgjeetu wrote on Fri, 11 October 2013 19:53
so there is printing mistake in question paper is it confirm?


Question paper?
Re: How to get this SQL output :- [message #598238 is a reply to message #598237] Fri, 11 October 2013 12:14 Go to previous messageGo to next message
jgjeetu
Messages: 240
Registered: July 2013
Location: India Delhi Rohini Sec-1
Senior Member

yeah i have attached scan copy of question paper in that comment , check it.

[Updated on: Fri, 11 October 2013 12:15]

Report message to a moderator

Re: How to get this SQL output :- [message #598240 is a reply to message #598221] Fri, 11 October 2013 12:17 Go to previous messageGo to next message
jgjeetu
Messages: 240
Registered: July 2013
Location: India Delhi Rohini Sec-1
Senior Member

i read that whole topic, there is mentioned about order by clause too but there is no example and nothing about how to use order by in that manner which i had mentioned the query , so not able to understand the concept.
Re: How to get this SQL output :- [message #598245 is a reply to message #597960] Fri, 11 October 2013 13:18 Go to previous message
jgjeetu
Messages: 240
Registered: July 2013
Location: India Delhi Rohini Sec-1
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 Smile


Previous Topic: *Action: Get the result as a lob instead
Next Topic: Performance with Materialized view fast refresh
Goto Forum:
  


Current Time: Thu Nov 27 13:48:41 CST 2014

Total time taken to generate the page: 0.40437 seconds