CODEWITHSUNDEEP
javasqloracle-database
Jeremy Peterson
Jeremy Peterson

Reputation: 15

Adding total of columns when another column changes

I am currently trying to create a java program that:

  1. Retrieves data from an Oracle sql database (String and BigDecimal values) and puts them in a resultSet
  2. Displays each row
  3. Adds a "totals" section at the end of each group, divided by 3 columns of String values.

I am having trouble with No.3 - I'm not sure how to tally up the totals depending on a change in another column.

To flesh out No.3... There are 7 columns, 1~3 String codes for department, store, and region IDs (String); 4 is an individual's ID (String); 5~7 the values for three different payments Salary, Overtime, and Adjusted total pay (BigDecimal). I need to add up and display the totals of each department, store, and region as they change (as the String value for each of them change): for example, the program would display each row of individuals in order, then display the department's totals when the department code changes. When both department and store change, both of those totals are displayed, one row after the other.

I have thought of using a treeMap but I can't get past whether I can sort depending on another column's values; and I can't think up a way to detect the change in department, store, and region ID codes...

It's a little over my head, I admit; and I would appreciate any help. Please let me know if you need more info - I feel like I've described what's needed but I might have missed something.

Thanks in advance!

Upvotes: 0

Views: 58

Answers (2)

Devon_C_Miller
Devon_C_Miller

Reputation: 16528

Take a look at Oracle's Analytic Functions.
For example: 

with tab1 as (
        select 'Dept 1' dept, 'Store 1' store, 'Region 1' region, 'ASmith' id, 101 salary,  1 overtime, 10 adjusted from dual union all
        select 'Dept 1' dept, 'Store 1' store, 'Region 1' region, 'BSmith' id, 102 salary,  2 overtime, 10 adjusted from dual union all
        select 'Dept 1' dept, 'Store 2' store, 'Region 1' region, 'CSmith' id, 104 salary,  4 overtime, 10 adjusted from dual union all
        select 'Dept 1' dept, 'Store 2' store, 'Region 1' region, 'DSmith' id, 108 salary,  8 overtime, 10 adjusted from dual union all
        select 'Dept 1' dept, 'Store 3' store, 'Region 1' region, 'DSmith' id, 108 salary,  8 overtime, 10 adjusted from dual union all
        select 'Dept 2' dept, 'Store 1' store, 'Region 1' region, 'DSmith' id, 108 salary,  8 overtime, 10 adjusted from dual union all
        select 'Dept 2' dept, 'Store 1' store, 'Region 1' region, 'ESmith' id, 116 salary, 16 overtime, 10 adjusted from dual union all
        select 'Dept 2' dept, 'Store 2' store, 'Region 1' region, 'FSmith' id, 132 salary, 32 overtime, 10 adjusted from dual union all
        select 'Dept 2' dept, 'Store 2' store, 'Region 1' region, 'ESmith' id, 116 salary, 16 overtime, 10 adjusted from dual union all
        select 'Dept 2' dept, 'Store 3' store, 'Region 1' region, 'FSmith' id, 132 salary, 32 overtime, 10 adjusted from dual
      )
select dept, store, region, sum(salary), sum(overtime), sum(adjusted)
  from tab1
 group by rollup(dept, store, region);

produces:

DEPT   STORE   REGION   SUM(SALARY) SUM(OVERTIME) SUM(ADJUSTED)
------ ------- -------- ----------- ------------- -------------
Dept 1 Store 1 Region 1     203             3            20
Dept 1 Store 1 **           203             3            20
Dept 1 Store 2 Region 1     212            12            20
Dept 1 Store 2 **           212            12            20
Dept 1 Store 3 Region 1     108             8            10
Dept 1 Store 3 **           108             8            10
Dept 1 **      **           523            23            50
Dept 2 Store 1 Region 1     224            24            20
Dept 2 Store 1 **           224            24            20
Dept 2 Store 2 Region 1     248            48            20
Dept 2 Store 2 **           248            48            20
Dept 2 Store 3 Region 1     132            32            10
Dept 2 Store 3 **           132            32            10
Dept 2 **      **           604           104            50
**     **      **          1127       127           100

where ** represents a NULL value

Upvotes: 1

axiopisty
axiopisty

Reputation: 5137

I don't think you need to approach 3 with Java. You should be able to use an sql "aggregate function" with a group by clause to get the total (sum) of the columns you're looking for.

See the aggregate function sum and the group by clause or check wikipedia's article about "aggregate functions".

Upvotes: 0

Related Questions