CODEWITHSUNDEEP
oracle-databaseexcelexcel-formulavba
Iftekhar
Iftekhar

Reputation: 365

how to display identical values in a single column using EXCEL or ORACLE

Hello I need a formula in column ‘C’ which calculates/adds the amount of B Column based on the column A ID. If there are several amounts in same ID it should add the total amount and would show the result in column ‘C’ as a single row.

image of sample data

the output can be obtained from Oracle SQL query or an Excel formula.your help would be appreciated.

Upvotes: 0

Views: 64

Answers (3)

Shravan Yadav
Shravan Yadav

Reputation: 1317

You can use rollup in oracle

Select id,amt,sum (amt) nullFrom table nullGroup by rollup (id,amt)

For more details see below link

https://oracle-base.com/articles/misc/rollup-cube-grouping-functions-and-grouping-sets

Upvotes: 1

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

You can get the same output from Oracle itself, using analytical functions like below.

SUM() OVER(PARTITION BY ... ) -> This actually do the cumulative sum

WITH MYTABLE(ID,AMT) AS
  (SELECT '2UF2', '500' FROM DUAL
  UNION ALL
  SELECT '2TC6', '300' FROM DUAL
  UNION ALL
  SELECT '2TC6', '200' FROM DUAL
        UNION ALL
        SELECT '2TC6', '800' FROM DUAL
  )
SELECT ID,
  AMT,
  CASE ROW_NUMBER() OVER(PARTITION BY ID ORDER BY NULL)
    WHEN 1
    THEN SUM(AMT) OVER(PARTITION BY ID ORDER BY NULL)
  END AS FORMULA
FROM MYTABLE
ORDER BY ID, FORMULA NULLS LAST;

SQL Fiddle Demo

Upvotes: 1

fgv
fgv

Reputation: 835

In SQL you need an aggregation function, in this case sum, and a group by clause. The generic query should look like the following:

Select sum(b) from table group by a

I hope this helps.

Upvotes: 0

Related Questions