How to substract value from its own column in same tabl through condition basis

Question

How to substract value from its own column in same tabl through condition basis

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2174 More actions · Answer 1

All right, some data. This could be my only chance to use SQL for the day. For my part, this week and until it's done, I'm working on web development. Another project which is not database development 🙂 Web development is like trying on someone else's old pair of shoes. Does it feel like new? New to me is not "new" in the modern sense. The whole concept of "applying styles" is something of a departure

Phil, why subtract the average? Seems like this could be conditionally summed. Also, I agree with what Jeff wrote

DROP TABLE IF EXISTS #TempSOR_mb;
GO
--===== Define the test table.
     -- This should include a PK if there is one.
     -- Looking at the inluded data, It would appear that the 
     -- "no" column is the PK here might be the PK but not sure.
CREATE TABLE #TempSOR_mb
(
    item_id SMALLINT
   ,subitem_id SMALLINT not null /* do not use IN with a nullable column */
   ,[no] SMALLINT primary key not null /* like Jeff wrote, pk suggested */
   ,lngt NUMERIC(18, 2)
   ,wid NUMERIC(18, 2)
   ,hd NUMERIC(18, 2)
   ,qty NUMERIC(18, 2),
);

INSERT #TempSOR_mb
(
    item_id 
   ,subitem_id
   ,[no] 
   ,lngt
   ,wid
   ,hd
   ,qty
)
VALUES
(101,290,1,0.10,0.20,0.20,0.00),
(103,201,2,0.20,0.30,0.30,0.04),
(103,202,3,0.30,0.40,0.40,0.14),
(103,203,4,0.40,0.50,0.50,0.40),
(104,299,5,0.50,0.60,0.70,1.05),
(107,204,6,0.50,0.70,0.80,1.68),
(107,205,7,0.60,0.40,0.80,1.34);

select item_id, sum(iif(subitem_id IN (203, 299), qty, 0)) net_qty, sum(qty) sum_qty
from #TempSOR_mb
group by item_id;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Phil Parkin SSC Guru Points: 246987 More actions · Answer 2

Steve Collins wrote:

Phil, why subtract the average? Seems like this could be conditionally summed. Also, I agree with what Jeff wrote

Because it's a CROSS JOIN, but I only want to subtract the (already aggregated) amount once from the summed amount. AVG is a trick to make that work.

This reply was modified 10 months, 3 weeks ago by Phil Parkin.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Steve Collins SSCrazy Points: 2174 More actions · Answer 3

We'll have to see how the OP replies. Maybe my code only summed the part they want subtracted from the sum_qty

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

manojsemwal1 40990 SSC Enthusiast Points: 190 More actions · Answer 4

Thanks Phil Its works fine for me

Thanks

Manoj

This reply was modified 10 months, 3 weeks ago by manojsemwal1 40990.