Post reply

Help with SQL Query

  • November 29, 2021 at 5:51 pm

    #3957969

    Hello SQL experts,

    Please see attached file for table detail.

    I have a temp table name company similar to what’s shown above  and I am trying to write a query that will SUM up the totaAmt column for each company then subtract that total from the company’s Bid_Limit of  25000  to find the Results.

    I cannot figure out how to get the combine / sum for the company that have different code

    What I am looking for is the result in the table shown below, the 3 companies with code of 1,4 are combined and summed, the code column do not need to be in the output table. Thanks

    Any help will be greatly appreciated

     

    Capture

    • This topic was modified 2 years, 11 months ago by  Hogie503.
    • This topic was modified 2 years, 11 months ago by  Hogie503.

    Attachments:
    You must be logged in to view attached files.

  • November 29, 2021 at 6:14 pm

    #3957982

    It's preferred to copy-paste schemas, code etc into your post instead of as an attachment. Many of us don't open attachments due to virus risks. Use the "Insert/Edit code sample" option.

  • November 29, 2021 at 6:26 pm

    #3957987

    Hi,

    Sorry rookie mistake, I edited the post to show the table info, hopefully that helps..Thanks

     

  • November 29, 2021 at 6:32 pm

    #3957989

    Ignore "code" and  GROUP BY on Company ID# ?

  • November 29, 2021 at 8:02 pm

    #3958409

    Hogie503 wrote:

    Hi,

    Sorry rookie mistake, I edited the post to show the table info, hopefully that helps..Thanks

    You've been around long enough to know this.  In my signature is a link that provides instructions.

    Does this work?

    WITH CTE
    AS
    (
    SELECT 
    YEAR, 
    COmpany, 
    CompanyID,
    Sum(TotalAmount), TotalAmount
    MAX(Bid_Limit) BidLimit
    FROM Table
    GROUP BY 
    YEAR, 
    COmpany, 
    CompanyID
    )

    SELECT 
    YEAR, 
    COmpany, 
    CompanyID,
    TotalAmount,
    BidLimit.
    BidLimit - TotalAmount Results
    FROM CTE

     

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • November 29, 2021 at 8:15 pm

    #3958411

    Thank you.. Really appreciate the help...

Viewing 6 posts - 1 through 5 (of 5 total)

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