Help with SQL Query

  • 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 1 month, 2 weeks ago by  Hogie503.
    • This topic was modified 1 month, 2 weeks ago by  Hogie503.
    Attachments:
    You must be logged in to view attached files.
  • 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.

  • Hi,

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

     

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

  • 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/

  • 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