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

  • 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

  • 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 9 months, 3 weeks ago by  Phil Parkin.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • 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

  • Thanks Phil Its works fine for me

    Thanks

     

    Manoj

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

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