asked on

How to use Sum-If Type function in SQL?

Hi. I want to add some values in SQL on the basis of some other condition(something like using sum-if in excel). Below is the example:

I have a table with three columns using which I want to calculate the total product value and store the value in a column with respect to Bound Status and keeping values for other statuses as Zero.

Input Table:

Product      Status      Value
ABC-01      Bound      10
ABC-02      End        12
ABC-03      Canc      -2
DEF-01      Bound      15
EFG-01      Bound      20
EFG-02      Canc      -15

Output Table:

Product      Status      Value      Final Value
ABC-01      Bound      10        20
ABC-02      End        12        0
ABC-03      Canc      -2        0
DEF-01      Bound      15        15
EFG-01      Bound      20        5
EFG-02      Canc      -15        0

Thanks

Ryan Chong

what's your database you're using?

can't really get the logic, can you elaborate further?

Éric Moreau

I don't understand how you get to the final value but a sum-if in SQL would be something like this:

select product, status, sum(case status when 'bound' the value else 0 end) as TotalValue from yourtablename group by product, status

Open in new window

Geert G

by your title i would use a case inside a sum
but the input and output suggest a different approach

with cte as (select substr(product, 1, 3) prod, sum(value) fv from input_table group by substr(product, 1, 3) )
select a.product, a.status, a.value, nvl(b.fv, 0) final_value
from input_table a
  left join cte b on (substr(a.product, 1, 3) = b.prod and a.status = 'BOUND')

Open in new window

sample

Geert G

or combine it ... this might give performance problems

select a.product, a.status, a.value, 
  case when status = 'Bound' then (
    select sum(value) from input_table b 
    where substr(a.product, 1, 3) = substr(b.product, 1, 3) ) else 0 end final_value
from input_table a

Open in new window

HainKurt

whats the logic for "Final Value" in your sample data?

ASKER CERTIFIED SOLUTION

Chris Luttrell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aakriti Choube

ASKER

Thanks Chris. It worked. But I have a doubt regarding 'Over' and 'Partition by'. What is the use of it and when should we use it? As I was using the similar queries except the last part..:)

SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Geert G

you'll probably get better performance if you split the product column into a base name and a number
unless you add a function index on the expression substr(product, 1, 3)

HainKurt

another approach using CTE and using the logic by Chris

with
MyTable as (
	select * from (
	VALUES 
	('ABC-01', 'Bound',      10	 ),
	('ABC-02', 'End',        12	 ),
	('ABC-03', 'Canc',      -2	 ),
	('DEF-01', 'Bound',      15	 ),
	('EFG-01', 'Bound',      20	 ),
	('EFG-02', 'Canc',      -15	 )
	) as myTable(Product, Status, Value)),
t as (select left(Product, CharIndex('-', Product)-1) PrdCtg, * from MyTable)
SELECT *,
      CASE WHEN T.Status = 'Bound'
	   THEN SUM(T.Value) OVER (PARTITION BY PrdCtg)
	   ELSE 0
	   END FinalValue
  FROM t
 ORDER BY PrdCtg, Product;

PrdCtg	Product	Status	Value	FinalValue
ABC	ABC-01	Bound	10	20
ABC	ABC-02	End	12	0
ABC	ABC-03	Canc	-2	0
DEF	DEF-01	Bound	15	15
EFG	EFG-01	Bound	20	5
EFG	EFG-02	Canc	-15	0

Open in new window

* remove line 2-11 and modify table name accordingly to use in your db

SOLUTION

Chris Luttrell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aakriti Choube

ASKER

Thanks Chris.

Ryan Chong