# Oracle - Select with math and groupings

I have a table that I'd like to run some calculations on but this select statement is above my oracle wheelhouse.

I wanted to attempt (as much of this) in oracle. But ultimately this will end up getting run through php to get output to the screen.

This is for Oracle 8i

I have 4 important pieces of data - (Customer Id, New_Item, Suggested_net, and Volume by year)

```customer_id = #
new_item = 0 or 1
suggested_net = #
year_1_n =  #
year_2_n =  #
year_3_n =  #
year_1_e =  #
year_2_e =  #
year_3_e =  #
```

new_items = 0 correlates only to year_1_e, year_2_e, year_3_e

new_items = 1 correlates only to year_1_n, year_2_n, year_3_n

Here's an example

```customer_id, new_item, suggested_net, year_1_n, year_2_n, year_3_n, year_1_e, year_2_e, year_3_e

2, 0, 4.25, null, null, null, 100, 100, 100;
2, 0, 5.25, null, null, null, 100, 100, 100;
3, 0, 2.50, null, null, null, 100, 100, 100;
1, 0, 3.50, null, null, null, 100, 100, 100;
2, 1, 5.99, 100, 200, 300, null, null, null;
3, 1, 4.99, 200, 400, 600, null, null, null;
```

So I need to get this info: For each row, add the volumes up (year_x...) * suggested_net, Then add that amount to a grouped customer_id by new_item = 0 or 1.

So using the above table

```Customer 2, new_item = 0 would have a sum of (1275 + 1575)
Customer 1, new_item = 0 would have a sum of 1050
Customer 2, new_item = 1 would have a sum of 3594
```

etc. etc.

Now this may or may not be possible or efficient to do in a select statement, but like I said I wanted to do as much of the heavy lifting with oracle, the rest I can handle with php.

```SELECT Coustomer_Id,
new_item,
SUM((CASE WHEN new_item = 1 THEN year_1_n + year_2_n + year_3_n
WHEN new_item = 0 THEN year_1_e + year_2_e + year_3_e
END ) * suggested_net) AS TotalSum
FROM Table1
GROUP BY Coustomer_Id,
new_item
```

OR

```SELECT Coustomer_Id,
new_item,
SUM(DECODE(new_item, 1, year_1_n + year_2_n + year_3_n
, 0, year_1_e + year_2_e + year_3_e
, 0) * suggested_net) AS TotalSum
FROM Table1
GROUP BY Coustomer_Id,
new_item
```

```select customer_id
, new_item
, sum((nvl(year_1_n, 0) +
nvl(year_2_n, 0) +
nvl(year_3_n, 0) +
nvl(year_1_e, 0) +
nvl(year_2_e, 0) +
nvl(year_3_e, 0)) *suggested_net) Total
from t1
group by customer_id
, new_item
```

### Populate Spinner from string array source, with a String ArrayAdapter

I have created my application by populating spinners within my Java code. I am now trying to convert it to populating my Spinners from my strings.xml file. I have followed a few websites( example 1...

### How to test Asp.Net Identity UserManger CreateAsync

I am trying to test for failure conditions of my Account controller. When i run the test in debug mode, i am not seeing an expected result. I am expecting to return a failed identity result when ...

###### About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.