Help with sql query

Question

Hello guys need help with a query. Consider the following table

I need to select first the sum of each Code from table. I am doing it with simple sum and group by statement. Then I have to subtract the results from each code sum where type='r'

1)Say for first part of query, we will get 2 rows from SUM (one with total USD and one with total YEN)

2) Now I need to subtract from these results the corresponding USD, YEN value which has Type='r'

Any clue guys ? I have to do it inside SQL and not a stored procedure.

Answer 1

Does it have to be a single query?

I'd say SUM the total, then SUM the subcategory where Type='r', then subtract one from the other.

You could do this in one line of SQL, but I'm pretty sure it would be either joining the table with itself or using a subquery. Either way, it's doing the same amount of work as the above.

Answer 2

Why not use a WHERE statement to say WHERE Type != 'r' so that those values never even get added to sum in the first place...

SELECT `Code`, SUM(`Amount`) AS `Total` 
  FROM `Table` 
 WHERE `Type` != 'r' 
 GROUP 
    BY `Code`;

Something like that.

Answer 3

select code, l.amount - r.amount
from
    (select code, sum(amount) as amount from my_table group by code) l
    left join (select code, sum(amount) as amount from my_table where type = 'r' group by code) r
    on l.code = r.code

Answer 4

You can do this in a single, simple query:

select
   code,
   sum(case when type = 'r' then (-1 * amount) else amount end) as sum
from 
   yourtable
group by
   code

Basically, you're changing the sign of the rows that have type = 'r', so when you sum all rows for a particular code you'll get the correct answer.

Answer 5

Try:

select code, 
       sum(amount) gross_total,
       sum(case when type = 'r' then amount else 0 end) type_r_total,
       sum(case when type != 'r' then amount else 0 end) net_total
from yourtable
group by code;

to see the overall totals, type R only totals and non-type R totals for each currency on one row per currency, in a single pass.