CODEWITHSUNDEEP
sql-serversql-server-2008
joelc
joelc

Reputation: 2761

Performing a SUM when value could exist in one of a few different columns

Assume there is a simple table:

CREATE TABLE [dbo].[foo](
    [foo_id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
        [val1_id] [int] NULL,
        [val1_amount] [int] NULL,
        [val2_id] [int] NULL,
        [val2_amount] [int] NULL,
        [val3_id] [int] NULL,
        [val3_amount] [int] NULL,
        [val4_id] [int] NULL
        [val4_amount] [int] NULL,
) ON [PRIMARY]

And there is some other table that is:

CREATE TABLE [dbo].[val](
    [val_id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
        [amount] [int] NOT NULL,
) ON [PRIMARY]

The user of the application will select an entry from the table val, and the application will place val_id and amount in val?_id and val?_amount in a non-deterministic manner (yes, I know this isn't good, but I have to deal with it).

Is there a way to produce output that will group by the val_id from the foo table and SUM the amount values from the foo table, given that the val_id/amount may be stored in any of the val?_id/val?_amount columns? Note that if val_id is stored in val1_id, the amount will always be stored in val1_amount within that row, but the same val_id may appear in a different val?_id in a different row (but the amount will be in the corresponding column)

Upvotes: 0

Views: 135

Answers (4)

RichardTheKiwi
RichardTheKiwi

Reputation: 107776

If you have correctly stated that the value will only ever appear in one pair of (val_id, val_amount) columns leaving the other 3 pairs blank, then this will work better than the currently accepted answer (@lamak's)

select id = coalesce(val1_id, val2_id, val3_id, val4_id),
       val = sum(coalesce(val1_amount, val2_amount, val3_amount, val4_amount))
from foo
group by coalesce(val1_id, val2_id, val3_id, val4_id);

It is easy to understand and even though the COALESCE is repeated, it is really only evaluated once so there is no performance penalty. The query plan is estimated at 50% the cost of the other query.

The currently accepted answer may also produce a phantom NULL row, for example for the data in my SQL fiddle demo that does not contain anything in the 3rd pair (val3_amount).

SQL Fiddle demo

Upvotes: 0

Lamak
Lamak

Reputation: 70668

Well, after you smacked the guy that designed this, you can try to UNPIVOT your columns, and then simply SUM the val_amount resultant column. I'm not gonna actually use UNPIVOT, but I'm doing the same with CROSS APPLY:

SELECT x.Val_id, SUM(x.val_amount) Val_Amount
FROM dbo.foo t
CROSS APPLY 
(
    VALUES
        (t.val1_id, t.val1_amount),
        (t.val2_id, t.val2_amount),
        (t.val3_id, t.val3_amount),
        (t.val4_id, t.val4_amount)
) x (Val_id, val_amount)
GROUP BY x.Val_id;

Here is a sqlfiddle with a live demo for you to try.

Upvotes: 6

Tim
Tim

Reputation: 8919

Is there a way to produce output that will group by the val_id from the foo table and SUM the amount values from the foo table, given that the val_id/amount may be stored in any of the val?_id/val?_amount columns?

Hell is other people's data. You might do something like this:

create view FOO as
select foo.id, val1_id as valueid, val1_amount as amt from T
union all
select foo.id  val2_id as valueid, val2_amount as amt from T
union all
select foo.id  val3_id as valueid, val3_amount as amt from T
union all
select foo.id  val4_id as valueid, val4_amount as amt from T

Upvotes: 0

Cargo23
Cargo23

Reputation: 3189

I did it using the COALESCE function. I'm not clear why I had to do a nested query, but I couldn't get it to take it otherwise: 

select id, sum(amount)
FROM 
   (SELECT COALESCE(val1_id , val2_id , val3_id , val4_id) id, 
       COALESCE(val1_amount , val2_amount , val3_amount , val4_amount) amount  
    from foo) coalesced_data 
GROUP BY ID

http://sqlfiddle.com/#!3/2ef35/6

Upvotes: 1

Related Questions