Home » SQL & PL/SQL » SQL & PL/SQL » dumb title.
dumb title. [message #254173] Thu, 26 July 2007 01:30 Go to next message
romi
Messages: 67
Registered: October 2006
Member
Hello all,

I have a table like:-

deptno sal

10 1000
20 2000
10 3000
30 4000
20 5000
40 6000


Now i want result as follows:-

deptno sal sum(sal)

10 1000 4000
20 2000 7000
10 3000 4000
30 4000 4000
20 5000 7000
40 6000 6000


Plz give me answer.it's not urgent, I'm just too damn lazy to find an answer myself. I also do not know how to spell.

[Updated on: Thu, 26 July 2007 07:49] by Moderator

Report message to a moderator

Re: Query [message #254175 is a reply to message #254173] Thu, 26 July 2007 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Nothing is urgent in forum, we reply when we want.

2/ The only urgent thing if for you to read the following BEFORE posting:
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

3/ Have a look at SUM analytic function. I let you search in the documentation.

Regards
Michel
Re: Query [message #254186 is a reply to message #254173] Thu, 26 July 2007 02:06 Go to previous messageGo to next message
tarunj
Messages: 23
Registered: April 2007
Location: Noida
Junior Member
TRY THIS
SELECT dept_id, sal, (SELECT sum( sal ) FROM dept d1 WHERE d1.dept_id = d.dept_id GROUP BY dept_id) AS sum_sal
FROM `dept` d
Re: Query [message #254189 is a reply to message #254186] Thu, 26 July 2007 02:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Will surely not work as there is no SAL column in DEPT table.

Regards
Michel
Re: Query [message #254190 is a reply to message #254189] Thu, 26 July 2007 02:12 Go to previous messageGo to next message
tarunj
Messages: 23
Registered: April 2007
Location: Noida
Junior Member
look above the structure of table
Re: Query [message #254191 is a reply to message #254189] Thu, 26 July 2007 02:12 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Michel has given the key: SUM(...) OVER (...) Wink. The analytic SUM() function.

MHE
Re: Query [message #254197 is a reply to message #254190] Thu, 26 July 2007 02:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
tarunj,
As OP does not name his table, I assume you're talking about SCOTT's one (otherwise why did you name DEPT your table?) which is likely the good option as DEPTNO and SAL are columns of SCOTT's EMP table.

Regards
Michel
Re: Query [message #254214 is a reply to message #254197] Thu, 26 July 2007 03:24 Go to previous messageGo to next message
tarunj
Messages: 23
Registered: April 2007
Location: Noida
Junior Member
I am giving the logic only.
Re: Query [message #254215 is a reply to message #254173] Thu, 26 July 2007 03:26 Go to previous messageGo to next message
ravik_mca03
Messages: 3
Registered: July 2007
Junior Member
hi romy,
u can check with this query
iam correcting it
it will work surely as for as iam concerned

select e.deptno,e.sal,(select sum(k.sal) from emp k where k.deptno=e.deptno) sum_sal from emp e

[Updated on: Thu, 26 July 2007 04:19]

Report message to a moderator

Re: Query [message #254217 is a reply to message #254215] Thu, 26 July 2007 03:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select e.deptno,e.sal,sum(k.sal) from emp e,emp k group by e.deptno,e.sal
  2  having k.deptno=e.deptno; 
having k.deptno=e.deptno
       *
ERROR at line 2:
ORA-00979: not a GROUP BY expression

Regards
Michel
Re: Query [message #254222 is a reply to message #254215] Thu, 26 July 2007 03:40 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
@Ravi,
that query will fail, I'm afraid. And even if you correct it, will it produce the correct results?

@tarunj: your logic is not bad, but my preference goes out to the analytic approach. That's a personal thing, and I would compare statistics before I made a final decision.

MHE
Re: Query [message #254229 is a reply to message #254173] Thu, 26 July 2007 03:58 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are 3 different ways to do it:
- analytic funtion
- scalar subquery
- subquery
SQL> select deptno, sal,
  2         sum(sal) over(partition by deptno) sum_sal
  3  from emp
  4  /
    DEPTNO        SAL    SUM_SAL
---------- ---------- ----------
        10       2450       8750
        10       5000       8750
        10       1300       8750
        20       2975      10875
        20       3000      10875
        20       1100      10875
        20        800      10875
        20       3000      10875
        30       1250       9400
        30       1500       9400
        30       1600       9400
        30        950       9400
        30       2850       9400
        30       1250       9400

14 rows selected.

SQL> select deptno, sal,
  2         (select sum(sal) from emp e2 where e2.deptno=e1.deptno) sum_sal
  3  from emp e1
  4  /
    DEPTNO        SAL    SUM_SAL
---------- ---------- ----------
        20        800      10875
        30       1600       9400
        30       1250       9400
        20       2975      10875
        30       1250       9400
        30       2850       9400
        10       2450       8750
        20       3000      10875
        10       5000       8750
        30       1500       9400
        20       1100      10875
        30        950       9400
        20       3000      10875
        10       1300       8750

14 rows selected.

SQL> select e1.deptno, e1.sal, e2.sum_sal
  2  from emp e1,
  3       (select deptno, sum(sal) sum_sal from emp group by deptno) e2
  4  where e2.deptno = e1.deptno
  5  /
    DEPTNO        SAL    SUM_SAL
---------- ---------- ----------
        20        800      10875
        30       1600       9400
        30       1250       9400
        20       2975      10875
        30       1250       9400
        30       2850       9400
        10       2450       8750
        20       3000      10875
        10       5000       8750
        30       1500       9400
        20       1100      10875
        30        950       9400
        20       3000      10875
        10       1300       8750

14 rows selected.

The best one (in performances) depends on cardinality and indexes.

Regards
Michel
Previous Topic: know the @
Next Topic: Cursor Vs Subquery
Goto Forum:
  


Current Time: Mon Dec 05 12:40:20 CST 2016

Total time taken to generate the page: 0.06806 seconds