CODEWITHSUNDEEP
mysqlsqlsql-serversql-server-2008t-sql
Furkat Kochkarow
Furkat Kochkarow

Reputation: 65

Subtracting One Row from Another

Please help me, I cant solve this problem for a whole day I have rows like 

Id   Serial  StockCode  StockTitle  ActionType  
1    123     D-005      Desk        Income      
2    345     C-005      Chair       Income      
3    123     D-005      Desk        Expense     
4    685     C-005      Chair       Expense     
5    345     C-005      Chair       Expense

The task is to calculate the stock serials balance if there are rows with ActionType-Income, insert a resulting row(using UNION or smth else). The result row should subtract one row from another with the same Serial. So in our case the resulting table should look something like

Id   Serial  StockCode  StockTitle  ActionType  
1    123     D-005      Desk        Income      
2    345     C-005      Chair       Income      
3    123     D-005      Desk        Expense     
4    685     C-005      Chair       Expense     
5    345     C-005      Chair       Expense  
___________________________________________
*    685     C-005      Chair       Balance 

The

Upvotes: 1

Views: 98

Answers (1)

erik258
erik258

Reputation: 16304

I built a fiddle that counts up the Incomes as +1 and expenses as -1, and gives you a total count per serial number. Built in MySQL but seems pretty generic. 

Select sum( IF(ActionType = 'Income', 1, -1) ) as Balance, 
 Serial, StockCode, StockTitle
 from Table1
GROUP BY serial;

ends up with

COUNT   SERIAL  STOCKCODE   STOCKTITLE
 0          123 D-005           Desk
 0          345 C-005           Chair
-1          685 C-005           Chair

Which I think is correct given your example.

http://sqlfiddle.com/#!2/ce8e0/1 sqlfiddle text-to-DDL import is dreamy :)

Upvotes: 1

Related Questions