Home » SQL & PL/SQL » SQL & PL/SQL » GROUP BY (ANY IDEA WILL BE HELPFUL.....)
GROUP BY [message #588845] Fri, 28 June 2013 16:45 Go to next message
D_ORA
Messages: 32
Registered: November 2012
Location: UK
Member
Hi all , I would like to see the result like the following from dept and emp table...
Dname   Empno  Sal.
IT      1001   $2300
         1001  $5600
Finance  2001   $3000
         2002   $3000
         2003   $4000

Any idea how to get the above result...I have tried by using the GROUP BY & join ...but not achieved the above one .
Thanks,

[EDITED by LF: applied [code] tags]

[Updated on: Sat, 29 June 2013 03:11] by Moderator

Report message to a moderator

Re: GROUP BY [message #588857 is a reply to message #588845] Sat, 29 June 2013 00:22 Go to previous messageGo to next message
Michel Cadot
Messages: 57609
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
break on dnmae


Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: GROUP BY [message #588904 is a reply to message #588857] Sat, 29 June 2013 10:48 Go to previous messageGo to next message
D_ORA
Messages: 32
Registered: November 2012
Location: UK
Member
Millions of thanks for your advice about code writing ...I will follow it...

Michel my scenario is like that .in my report I want to write the query where I can group by plan...e.g.

Group Status Plan
1 Active Eyes Treatment
Dentistry
Travel

2 Active Travel
Dentistry

.....................
.......................

[Updated on: Sat, 29 June 2013 11:05]

Report message to a moderator

Re: GROUP BY [message #588905 is a reply to message #588904] Sat, 29 June 2013 10:58 Go to previous messageGo to next message
Michel Cadot
Messages: 57609
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> break on deptno
SQL> select deptno, empno, sal
  2  from emp
  3  order by 1, 2
  4  /
    DEPTNO      EMPNO        SAL
---------- ---------- ----------
        10       7782       2450
                 7839       5000
                 7934       1300
        20       7369        800
                 7566       2975
                 7788       3000
                 7876       1100
                 7902       3000
        30       7499       1600
                 7521       1250
                 7654       1250
                 7698       2850
                 7844       1500
                 7900        950

Regards
Michel
Re: GROUP BY [message #588907 is a reply to message #588905] Sat, 29 June 2013 11:15 Go to previous messageGo to next message
D_ORA
Messages: 32
Registered: November 2012
Location: UK
Member

Michel my scenario is like that .in my report I want to write the query where I can group by plan...e.g.
Group Status Plan
1 Active Eyes Treatment
Dentistry
Travel...

again sorry about bit mess up in the output...I have tried in your provided link ..but not succeeded to get the result as you get below..I will go through in more detail for formatting the code...
Re: GROUP BY [message #588908 is a reply to message #588907] Sat, 29 June 2013 11:16 Go to previous messageGo to next message
BlackSwan
Messages: 21942
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: GROUP BY [message #588909 is a reply to message #588908] Sat, 29 June 2013 11:26 Go to previous messageGo to next message
D_ORA
Messages: 32
Registered: November 2012
Location: UK
Member
Michel Thanks for providing me useful links I am going to read these info.

One more thing , I have tried it by using the GROUP BY and JOIN(because I am interested to pick the Policy Plan from the Policy table...)
but not able to achieved my result ...infact I am not interested to group other columns like Group , Status ...I want the query to group by Plan ...
any idea with the help of example will be very helpful....

Thanks in advance for your time.
Re: GROUP BY [message #588910 is a reply to message #588909] Sat, 29 June 2013 11:44 Go to previous messageGo to next message
BlackSwan
Messages: 21942
Registered: January 2009
Senior Member
I don't know what you have.
I don't know what you do.
I don't know what you see.
It is really, Really, REALLY difficult to fix a problem that can not be seen.
use COPY & PASTE so we can see what you do & how Oracle responds.
Re: GROUP BY [message #588911 is a reply to message #588910] Sat, 29 June 2013 11:58 Go to previous messageGo to next message
D_ORA
Messages: 32
Registered: November 2012
Location: UK
Member
I tried to achieved the result like below but not succeeded...
./fa/10945/0/
  • Attachment: sql.png
    (Size: 2.60KB, Downloaded 84 times)

[Updated on: Sat, 29 June 2013 12:00]

Report message to a moderator

Re: GROUP BY [message #588912 is a reply to message #588911] Sat, 29 June 2013 12:49 Go to previous message
BlackSwan
Messages: 21942
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

I don't know what you have.
I don't know what you do.
I don't know what you see.
It is really, Really, REALLY difficult to fix a problem that can not be seen.
use COPY & PASTE so we can see what you do & how Oracle responds.
Previous Topic: Update data in database
Next Topic: Cumulative Sum of previous row in the same column
Goto Forum:
  


Current Time: Fri Apr 18 09:40:14 CDT 2014

Total time taken to generate the page: 0.13315 seconds