compute sum

[oselame]

Hi!

How I do "compute sum..." in pl/sql developer?

Thank

 

[Marco Kalter]

What exactly do you want to compute the sum of?

------------------
Marco Kalter
Allround Automations

 

[oselame]

I need to add an one column sql.

Example

Field1 Field2
------ ------
1 50
2 50
3 50

Compute sum(Field2) = 150

 

[aotte]

Originally posted by oselame:
I need to add an one column sql.

Example

Field1 Field2
------ ------
1 50
2 50
3 50

Compute sum(Field2) = 150

I am assuming you do not mean the SQL [SELECT SUM(field2) FROM table_name;], but you mean on a result set already in the grid. I guess it depends on the size:
0-10 rows)
Give the old grey cells a good exercise
10-20 rows)
Whip out the calculator [CTRL+ESC | R | calc | Enter]
20 - 10,000 / 65535

rows)
Export to Excel and sum it there (might want to be patient though on those results with more than 10,000 rows)
10,000 - )
Start looking into the analytical functionality in Oracle. Haven't worked with it yet, I heard it can do running totals, which would give you your number on the last record. Or simply run the separate SQL mentioned at the start and bite the bullet on run time of the query.

Does that help?

------------------
Hakuna Matata,

Arnoud.

[This message has been edited by aotte (edited 04 June 2003).]

 

[oselame]

Hi.

In Sql Query Analyzer (SQL-Server) is:

Select Field1, Field2 from Table1
Compute sum(Field2)

Result is:

Field1 Field2
------ ------
1 50
2 30
3 20
4 60

Sum
------------
160

 

[aotte]

Hi,

I suggest that you check out the Oracle newsgroups, or OTN forums. They might have more/better solutions, and probably provide a better platform for this particular question.

I don't know of any comparable functionality in Oracle, or PL/SQL Developer, but here are three alternatives I can think of that might be acceptable to you.

CREATE TABLE sum_test (field1 VARCHAR2(3), field2 NUMBER);

INSERT INTO sum_test VALUES ('A',10);
INSERT INTO sum_test VALUES ('B',20);
INSERT INTO sum_test VALUES ('C',30);
COMMIT;

SELECT
   field1,
   field2,
   SUM(field2) OVER (ORDER BY field2 RANGE UNBOUNDED PRECEDING) cum_total
 FROM
   sum_test
;

-- Result:
   	FIELD1	FIELD2	TOTAL
1	A	10	10
2	B	20	30
3	C	30	60
;

SELECT
   field1,
   SUM(field2) field2
FROM
   sum_test
GROUP BY
   field1
UNION ALL
SELECT
   'SUM' field1,
   SUM(field2) field2
FROM
   sum_test
;

--Result:
   	FIELD1	FIELD2
1	A	10
2	B	20
3	C	30
4	SUM	60
;

SELECT
   SUM(field2) field2
FROM
   (SELECT
      field1,
      SUM(field2) field2
   FROM
      sum_test
   GROUP BY
      field1
   )
;

--Result:
   	FIELD2
1	60

------------------
Hakuna Matata,

Arnoud.

 

[amaury]

I have a kind of solution that works with Oracle 9i:

select Field1,Field2,sum(Field2) from Table1
group by rollup(Field1,Field2)
having grouping(Field1)=grouping(Field2);

ROLLUP, used in GROUP BY clauses, makes Oracle compute and return additional rows with the sum at different levels.
GROUPING indicates, if a row is an "additional row" computed by ROLLUP. I use it to discard intermediate sums.

 

[aotte]

Slightly simpler maybe:

SELECT
   field1,
   field2,
   SUM(field2) OVER () cum_total
FROM
   sum_test;

IOUG members, check out the Live 2003 presentation from Ken Guion on analytical functions. Very nice to the point explanation of the features, while elaving room to learn yourself.

------------------
Hakuna Matata,

Arnoud.